Remove numbers before the comma

Question

I have a numeric sequence in string type '0,1;2,2;5,3;8,4;'.

I need the results to come out '1,2,3,4' only the numbers after the comma.

I’ve tried the following consultation:

{select replace(replace('0,1;2,2;5,3;8,4;', '[0-9'],', ''), ';', ',')}

But it does not replace anything. How do I use the operator that identifies any numeral?

Answer 1

The solution I am proposing requires each line to have a unique identification. Therefore, for the purpose of demonstrating the solution, it will be considered that such unique identification exists.

-- código #1
CREATE TABLE tabela (ID_sequencia tinyint identity, Sequencia varchar(100));
INSERT into tabela values ('0,10;2,2;5,13;8,4');
go

To separate the elements will be used the user function Delimitedsplit8k, authored by Jeff Moden. Information about this function is available in the article An Improved SQL 8K "CSV Splitter" Function, the source code of which may also be obtained in Split a list of values.

The first step is to separate each pair:

-- código #2
SELECT ID_sequencia, T.sequencia, S1.ItemNumber as seqPar, S1.Item as Par
  from tabela as T 
       cross apply dbo.DelimitedSplit8K (T.sequencia, ';') as S1;

After separating each pair, each element of each pair should be obtained:

-- código #3
with
gPar as (
SELECT ID_sequencia, T.sequencia, S1.ItemNumber as seqPar, S1.Item as Par
  from tabela as T 
       cross apply dbo.DelimitedSplit8K (T.sequencia, ';') as S1
),
gElemento as (
SELECT P.ID_Sequencia, P.sequencia, P.seqPar, 
       S2.ItemNumber as seqElemento, S2.Item as Elemento
  from gPar as P
       cross apply dbo.DelimitedSplit8K (P.Par, ',') as S2
)
SELECT ID_Sequencia, sequencia, seqPar, seqElemento, Elemento
  from gElemento 
  order by ID_Sequencia, seqPar;

Having each separate element, the final step is to group the final elements of each pair in the order of each pair. Here is the complete SQL code, using the function STRING_AGG to group the elements:

-- código #4
-- Autor: José Diz 
with
gPar as (
SELECT ID_sequencia, T.sequencia, S1.ItemNumber as seqPar, S1.Item as Par
  from tabela as T 
       cross apply dbo.DelimitedSplit8K (T.sequencia, ';') as S1
),
gElemento as (
SELECT P.ID_Sequencia, P.sequencia, P.seqPar, 
       S2.ItemNumber as seqElemento, S2.Item as Elemento
  from gPar as P
       cross apply dbo.DelimitedSplit8K (P.Par, ',') as S2
)
SELECT ID_Sequencia, 
       string_agg (Elemento, ',') within group (order by seqPar)
  from gElemento
  where seqElemento = 2
  group by ID_Sequencia;

---

Since the function STRING_AGG only works from the 2017 version of SQL Server, it can be replaced by the technique blackbox xml in previous versions of SQL Server. Details in article Concatenation of several lines in the same column.

-- código #5 v2
-- Autor: José Diz 
with
gPar as (
SELECT ID_sequencia, T.sequencia, S1.ItemNumber as seqPar, S1.Item as Par
  from tabela as T 
       cross apply dbo.DelimitedSplit8K (T.sequencia, ';') as S1
),
gElemento as (
SELECT P.ID_Sequencia, P.sequencia, P.seqPar, 
       S2.ItemNumber as seqElemento, S2.Item as Elemento
  from gPar as P
       cross apply dbo.DelimitedSplit8K (P.Par, ',') as S2
)
SELECT E.ID_Sequencia, 
       --string_agg (Elemento, ',') within group (order by seqPar)
       stuff ((SELECT ',' + T.Elemento
                 from gElemento as T
                 where T.ID_Sequencia = E.ID_Sequencia
                       and T.seqElemento = 2
                 order by T.seqPar
                 for xml path(''), TYPE).value('.', 'varchar(max)'),
              1, 1, '') as novaSequencia       
  from gElemento as E
  where E.seqElemento = 2
  group by E.ID_Sequencia;