CODEWITHSUNDEEP
sqlsql-servert-sql
Dimitry
Dimitry

Reputation: 287

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

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?

Upvotes: 3

Views: 1769

Answers (5)

Dimitry
Dimitry

Reputation: 287

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);
}

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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 |

Upvotes: 2

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Hope this will help.

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

  1. Parse the string to table values
  2. Used Substring function to remove values after ':'
  3. 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

Upvotes: 0

user3864233
user3864233

Reputation: 514

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('''')')

Upvotes: 1

Jesuraja
Jesuraja

Reputation: 3844

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

Upvotes: 0

Related Questions