How do I replace consecutive commas in a column with single comma in MS SQL?
For example, I have data like
a,,,,b,,,c,,,,,,
d,e,,,f,,,,,,g,,
I want this to be processed to following format:
a,b,c,
d,e,f,g,
The suggested duplicate, Use SQL to Replace Multiple Commas in a String with a Single Comma, is for Oracle. This is a question about SQL Server.
You could use simple REPLACE:
SELECT c, REPLACE(REPLACE(REPLACE(c, ',', '~,'), ',~', ''), '~,', ',')
FROM tab;
Output:
┌──────────────────┬──────────┐
│ c │ result │
├──────────────────┼──────────┤
│ a,,,,b,,,c,,,,,, │ a,b,c, │
│ d,e,,,f,,,,,,g,, │ d,e,f,g, │
└──────────────────┴──────────┘
Please note that this approach does not depend on SQL dialect and should work with MySQL/Oracle/PostgreSQL/...
It's quite easy to do it with CTE:
declare @s varchar(20) = 'a,,,,b,,,c,,,,,, d,e,,,f,,,,,,g,,'
;with cte as (
select replace(@s, ',,', ',') [s], 1 [rn]
union all
select replace(s, ',,', ',') [s], [rn] + 1
from cte
where LEN(s) - LEN(replace(s, ',,', '')) > 0
)
select top 1 @s = s from cte
order by rn desc
select @s
Although there are very good answers (personally I'd tend to lad2025) I'd like to post another approach (especially as extension to the answer by DruvJoshi)
DECLARE @tbl TABLE(s VARCHAR(100))
INSERT INTO @tbl VALUES('d,e,,,f,,,,,,g,,')
,('a,,,,b,,,c,,,,,,');
SELECT CAST('<x>'+REPLACE(s,',','</x><x>')+'</x>' AS XML)
.query('for $x in /x[text()]
return
<x>
{
concat($x, ",")
}
</x>
')
.value('.','nvarchar(max)') AS result
FROM @tbl;
The solution uses the well-known XML trick to split a string. The rest is XQuery. The predicate /x[text()] will reduce the nodes to the ones with content. They will be re-created with a comma appended. The .value() with an XPath of . will return one single string of all content within the XML.
please use
SELECT
REGEXP_REPLACE(
REGEXP_REPLACE('Hi,,How are you? Fine, thanks ,, , ,,,, , James,Arden.', ', | ,', ','), --Replace ', ' and ' ,' with ','
',{1,}', ', ') single_comma_text --Replace one or more comma with comma followed by space
FROM DUAL;
Try this Script which eliminates space,multiple commas and give single comma separated as result
DECLARE @tbl AS TABLE (data nvarchar(max))
INSERT INTO @tbl
SELECT 'a,,,,b,,,c,,,,,, ,,,, ,,, ,, ,,,,,d,,,,,,,, ,, d,e,,,f,,,,,,g,,'
;WITH CTE
AS
(
SELECT data
,CAST(LEFT(data,1) AS VARCHAR(10)) AS Letter
,RIGHT(Data,LEN(Data)-1) AS Remainder
FROM @tbl
WHERE LEN(data)>1
UNION ALL
SELECT data
,CAST(LEFT(Remainder,1) AS VARCHAR(10)) AS Letter
,RIGHT(Remainder,LEN(Remainder)-1) AS Remainder
FROM CTE
WHERE LEN(Remainder)>0
)
SELECT STUFF((SELECT ', '+ Letter
FROM
(
SELECT Letter
FROM CTE
WHERE Letter <>',' AND Letter <>''
)dt FOR XML PATH ('')),1,1,'') AS RequiredOutPut
Result
RequiredOutPut
------------------
a, b, c, d, d, e, f, g
If you can have unknown number of commas I'd recommend splitter using XML path like below.
Assuming you have a table T with column c
also see working demo
Explanation is inline as comments
/* first we split out each letter from each column using XML Path after replacing commas with empty nodes */
; with cte as (
select
id,s
from
(
select
id,
xmldata=cast('<x>'+replace(c,',','</x><x>')+'</x>' as xml) -- conversion to XML from varchar
from t
)A
cross apply
(
select
s = data.D.value('.','varchar(100)')
FROM
A.xmldata.nodes('x') AS data(D)
)c
where s <>''-- filter out empty nodes i.e. commas
)
/* Now we join back results from CTE by adding single comma between letters*/
select distinct id, stuff
((select ','+ s
from cte c1
where c1.id =c2.id
for xml path ('')),1,1,'')
from cte c2
This is what i did.
select replace(replace(replace('a,,,b,,,c,d,e,,,,f',',','<>'),'><',''),'<>',',')
Here is the simple one to suffice all the below cases:
select REGEXP_REPLACE(REGEXP_REPLACE(',,LE,,EN,,A,,,','^,*|,*$',''),',{1,}', ', ');
Output :
LE, EN, A
