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?

Answer 1

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 |