T-SQL - remove chars from string beginning from specific character

Question

from table I retrieves values, for example,

7752652:1,7752653:2,7752654:3,7752655:4

or

7752941:1,7752942:2

i.e. string may contain any quantity of substrings. What I need: remove all occurrences of characters from char ':' to a comma char. For example,

7752652:1,7752653:2,7752654:3,7752655:4

should be

7752652,7752653,7752654,7752655

How do it?

Can we assume that the numbers (both before and after the colon) can be different lengths? — DavidG, Jul 26 '14 at 11:31
Cross apply on a split function, remove the stuff after the `:` then feed all that into XML PATH to reassemble the string. Or just use CLR and regular expressions replace. — Martin Smith, Jul 26 '14 at 11:31
If I had a penny for each comma separated value SQL question... anyway, what do you mean by "remove"? Remove from the field as in updating the original data, or just select the values with the values removed in the result? — Joachim Isaksson, Jul 26 '14 at 11:31
Though actually split functions are just procedural code so might as well just write a scalar function that does the split and string concatenation directly and returns the reassembled string. — Martin Smith, Jul 26 '14 at 11:37
this values already participate in outer apply, so I need to make replacements "on the fly" — Dimitry, Jul 26 '14 at 12:09
If a query is being used to generate this list, you can probably modify that query. If not, you should learn not to store comma delimited fields in string fields in a table. Learn to use junction tables. — Gordon Linoff, Jul 26 '14 at 13:15

score 2 · Answer 1 · answered Jul 27 '14 at 15:40

Replace : with start tag <X>.
Replace , with end tag </X> and an extra comma.
Add an extra end tag to the end </X>.

That will give you a string that look like 7752941<X>1</X>,7752942<X>2</X>.

Cast to XML and use query(text()) to get the root text values.
Cast the result back to string.

SQL Fiddle

MS SQL Server 2012 Schema Setup:

create table T
(
  C varchar(100)
)

insert into T values
('7752652:1,7752653:2,7752654:3,7752655:4'),
('7752941:1,7752942:2')

Query 1:

select cast(cast(replace(replace(T.C, ':', '<X>'), ',', '</X>,')+'</X>' as xml).query('text()') as varchar(100)) as C
from T

Results:

|                               C |
|---------------------------------|
| 7752652,7752653,7752654,7752655 |
|                 7752941,7752942 |

score 1 · Answer 2 · answered Jul 26 '14 at 18:03

declare @query varchar(8000)
select @query= 'select '+ replace (
            replace('7752652:1,7752653:2,7752654:3,7752655:4',',',' t union all select ')
            ,':',' t1 , ')
exec(';with cte as ( '+@query+' ) select cast(t1 as varchar)+'','' from cte for xml path('''')')

score 0 · Answer 3 · answered Jul 26 '14 at 17:21

Try this:

DECLARE @Data VARCHAR(100) = '7752652:1,7752653:2,7752654:3,7752655:4'
DECLARE @Output VARCHAR(100) = ''

WHILE CHARINDEX(':', @Data) > 0
BEGIN
    IF LEN(@Output) > 0 SET @Output = @Output + ','
    SET @Output = @Output + LEFT(@Data, CHARINDEX(':', @Data)-1)

    SET @Data = STUFF(@Data, 
                      1,  
                      (CASE CHARINDEX(',', @Data) 
                            WHEN 0 THEN LEN(@Data) 
                            ELSE CHARINDEX(',', @Data) 
                            END) - CHARINDEX(':', @Data),
                       '')
END

SELECT @Output AS Result -- 7752652,7752653,7752654,7752655

score 0 · Answer 4 · answered Jul 27 '14 at 15:03

Hope this will help.

I borrowed the Splitter function from here. You could use any delimiter parser you may already be using.

Parse the string to table values
Used Substring function to remove values after ':'
Use For xml to re-generate CSV

Test Data:'

IF OBJECT_ID(N'tempdb..#temp')>0
DROP TABLE #temp

CREATE TABLE #temp (id int, StringCSV VARCHAR(500))
INSERT INTO #temp VALUES ('1','7752652:1,7752653:2,7752654:3,7752655:4')
INSERT INTO #temp VALUES ('2','7752656:1,7752657:3,7752658:4')
INSERT INTO #temp VALUES ('3','7752659:1,7752660:2')
SELECT * FROM #temp t

Main Query:

;WITH cte_Remove(ID, REMOVE) AS 
(
    SELECT   y.id   AS ID,
             SUBSTRING(fn.string, 1, CHARINDEX(':', fn.string) -1) AS Removed
    FROM     #temp  AS y
    CROSS APPLY dbo.fnParseStringTSQL(y.StringCSV, ',') AS fn
)
SELECT   DISTINCT ID,
         STUFF(
                (
                 SELECT  ',' + REMOVE
                 FROM    cte_Remove AS t2
                 WHERE   t2.ID = t1.ID
                         FOR XML PATH('')
                ),1,1,'') AS col2
FROM     cte_Remove  AS t1

Cleanup Test Data:

IF OBJECT_ID(N'tempdb..#temp') > 0
    DROP TABLE #temp

score 0 · Accepted Answer · answered Jul 28 '14 at 10:00

I solved this problem with CLR function. It is more quickly and function can be used in complex queries

public static SqlString fnRemoveSuffics(SqlString source)
{
    string pattern = @":(\d+)";
    string replacement = "";
    string result = Regex.Replace(source.Value, pattern, replacement);

    return new SqlString(result);
}

T-SQL - remove chars from string beginning from specific character

5 Answers5