I have one column for comment and I need to show this for one report. Here what happen some time, users uses multiple enters in comment box. I can not access code part I need to manage this thing in SQL only.
我有一栏供评论,我需要在一份报告中显示。这里发生了一些时间,用户使用多个进入评论框。我无法访问我只需要在SQL中管理这个东西的代码部分。
So I have removed unwanted
所以我删除了不需要的
1 /r/n
2 /n/n
from using
REPLACE(REPLACE(Desc, CHAR(13)+CHAR(10), CHAR(10)),CHAR(10)+CHAR(10), CHAR(10)) as Desc,
Now I want to remove any \r or \n from starting or ending of the string if any
现在我想从字符串的开头或结尾删除任何\ r或\ n,如果有的话
3 个解决方案
#1
2
By the way you meant in your question:(Remove char(10) or char(13) from specific string)
Note: You should see the output result by switching your resultset output to Results to Text(Ctrl+T).
顺便提一下你的问题:(从特定字符串中删除char(10)或char(13))注意:您应该通过将结果集输出切换到结果到文本(Ctrl + T)来查看输出结果。
Results to Text
结果到文本
Results to Grid
结果到网格
#2
1
Use TRIM check here
在这里使用TRIM检查
Example : UPDATE tablename SET descriptions = TRIM(TRAILING "<br>" FROM descriptions) if you want to replace newline then use something like below
示例:UPDATE tablename SET description = TRIM(TRAILING“
”FROM说明)如果要替换换行,则使用下面的内容
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
or
DECLARE @testString varchar(255)
set @testString = 'MY STRING '
/*Select the string and try to copy and paste into notepad and tab is still there*/
SELECT testString = @testString
/*Ok, it seems easy, let's try to trim this. Huh, it doesn't work, the same result here.*/
SELECT testStringTrim = RTRIM(@testString)
/*Let's try to get the size*/
SELECT LenOfTestString = LEN(@testString)
/*This supposed to give us string together with blank space, but not for tab though*/
SELECT DataLengthOfString= DATALENGTH(@testString)
SELECT ASCIIOfTab = ASCII(' ')
SELECT CHAR(9)
/*I always use this like a final solution*/
SET @testString = REPLACE(REPLACE(REPLACE(@testString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') SELECT @testString
/*
CHAR(9) - Tab
CHAR(10) - New Line
CHAR(13) - Carriage Return
*/
#3
0
select dbo.trim('abc','c') -- ab
select dbo.trim('abc','a') -- bc
select dbo.trim(' b ',' ') -- b
Create a user-define-function: trim()
创建用户定义函数:trim()
从两侧修剪
修剪任何字母:空格,\ r,\ n等
Create FUNCTION Trim
(
@Original varchar(max), @letter char(1)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @rtrim varchar(max)
SELECT @rtrim = iif(right(@original, 1) = @letter, left(@original,datalength(@original)-1), @original)
return iif( left(@rtrim,1) = @letter, right(@rtrim,datalength(@rtrim)-1),@rtrim)
END
#1
2
By the way you meant in your question:(Remove char(10) or char(13) from specific string)
Note: You should see the output result by switching your resultset output to Results to Text(Ctrl+T).
顺便提一下你的问题:(从特定字符串中删除char(10)或char(13))注意:您应该通过将结果集输出切换到结果到文本(Ctrl + T)来查看输出结果。
Results to Text
结果到文本
Results to Grid
结果到网格
#2
1
Use TRIM check here
在这里使用TRIM检查
Example : UPDATE tablename SET descriptions = TRIM(TRAILING "<br>" FROM descriptions) if you want to replace newline then use something like below
示例:UPDATE tablename SET description = TRIM(TRAILING“
”FROM说明)如果要替换换行,则使用下面的内容
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
or
DECLARE @testString varchar(255)
set @testString = 'MY STRING '
/*Select the string and try to copy and paste into notepad and tab is still there*/
SELECT testString = @testString
/*Ok, it seems easy, let's try to trim this. Huh, it doesn't work, the same result here.*/
SELECT testStringTrim = RTRIM(@testString)
/*Let's try to get the size*/
SELECT LenOfTestString = LEN(@testString)
/*This supposed to give us string together with blank space, but not for tab though*/
SELECT DataLengthOfString= DATALENGTH(@testString)
SELECT ASCIIOfTab = ASCII(' ')
SELECT CHAR(9)
/*I always use this like a final solution*/
SET @testString = REPLACE(REPLACE(REPLACE(@testString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') SELECT @testString
/*
CHAR(9) - Tab
CHAR(10) - New Line
CHAR(13) - Carriage Return
*/
#3
0
select dbo.trim('abc','c') -- ab
select dbo.trim('abc','a') -- bc
select dbo.trim(' b ',' ') -- b
Create a user-define-function: trim()
创建用户定义函数:trim()
从两侧修剪
修剪任何字母:空格,\ r,\ n等
Create FUNCTION Trim
(
@Original varchar(max), @letter char(1)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @rtrim varchar(max)
SELECT @rtrim = iif(right(@original, 1) = @letter, left(@original,datalength(@original)-1), @original)
return iif( left(@rtrim,1) = @letter, right(@rtrim,datalength(@rtrim)-1),@rtrim)
END