如何从SQL Server中的字符串中删除前导和尾随逗号? [英] How do I remove leading and trailing commas from string in SQL server?
问题描述
我想删除出现在字符串开头和SQL SERVER中字符串末尾的所有逗号。我不想删除在STRING中出现的公告。
我的字符串就像 -
,test1,test2标签,test3标签,,,
必填o / p =>
test1,test2标签,test3标签
请帮帮我。谢谢。
我尝试了什么:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(' ,, test1,test2标签,test3标签,',' ,' ,' '))),' ',' ,')
但它给出了o\p =>
test1test2 tagtest3 tag
这是错误的,因为它正在删除字符串中的所有逗号。
检查这个 SQL Server TRIM字符 [ ^ ]
< blockquote>有几种方法可以做到这一点。例如,您可以使用像这样的小型T-SQL块
BEGIN
DECLARE @ data VARCHAR ( 100 跨度>);
SET @ data = ' ,, test1,test2 tag,test3 tag ,,,';
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING)( @ data ,PATINDEX( %[^,]%', @ data ) , 99999 )),PATINDEX(' %[^, ]%',REVERSE(SUBSTRING( @ data ,PATINDEX(' %[^,]%', @ data ), 99999 ))), 99999 ));
END ;
或者将逻辑嵌入到select语句中,例如
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING)(MyColumn,PATINDEX(' %[^,]%',MyColumn), 99999 )),PATINDEX(< span class =code-string>' %[^,]%',REVERSE(SUBSTRING(MyColumn,PATINDEX(' %[^,]%',MyColumn), 99999 ) )), 99999 ))
FROM MyTable
但是,简单的方法是为任务创建一个小函数,例如 TrimChar删除所需的字符 [ ^ ]
I want to remove ALL the commas that appear at the beginning of the string and at the end of the string in SQL SERVER. I DON'T WANT TO REMOVE COMMAS WHICH APPEAR WITHIN STRING.
My string is like-
",,test1,test2 tag,test3 tag,,,"
Required o/p=>
"test1,test2 tag,test3 tag"
Please help me. Thank you.
What I have tried:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(',,test1,test2 tag,test3 tag,', ',', ''))), '', ',')
But it gives o\p=>
"test1test2 tagtest3 tag"
which is wrong as it's removing all the commas which are within the string also.
check this SQL Server TRIM character[^]
There are several ways of doing this. For example you can use a small T-SQL block like this
BEGIN DECLARE @data VARCHAR(100); SET @data = ',,test1,test2 tag,test3 tag,,,'; SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999))),99999)); END;
Or embed the logic into a select statement, for example
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999))),99999)) FROM MyTable
However, the easy way would be to create a small function for the task, for example TrimChar to remove desired character[^]
这篇关于如何从SQL Server中的字符串中删除前导和尾随逗号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!