Select only numeric and ";"

September 8, 2023

I have a sample dataset in a table. I want to make changes while selecting in this data set. How can I do it?

Example table:

ID VAL
1 1.abc; 2.bcd; 3.a-sd; 4.fhg
2 5.bsfacd; 3.afgh-sd; 4.fvbnbhg

The result I want is:

ID VAL
1 1;2;3;4;
2 5;3;4;

How can I get this result with a select statement?

>Solution :

Ok so while you know storing multiple atomic values in a column is probably one of the biggest anti-patterns, one possible solution for your situation is to make use of translate and replace:

with dodgy as (
  select * from (values
    (1, '1.abc; 2.bcd; 3.a-sd; 4.fhg'),
    (2, '5.bsfacd; 3.afgh-sd; 4.fvbnbhg')
  )d(Id, Val)
)
select * , Concat(
    Replace(Translate(
      Val, 'abcdefghijklmnopqrstuvwxyz.-', 
      Replicate(' ', 28)
    ), ' ',''), ';'
  ) StillDodgy
from dodgy;