Skip to content
Advertisement

Extract number before and after character

I need to extract a certain number from my ‘TblA’ from column ‘Input’ in sql. I have the following example input and desired output 1 and 2.

Input Desired Output 1 Desired Output 2
20 x 88 nc. 20 88
100 x 300 nc 100 300
200x 88 nc. 200 88
5x 300 nc 5 300
ol (200x 88nc.) 200 88
ol (100x 300nc) 100 300
90dfa (45×65) 45 65
90dfa (45 x 65) 45 65
5,5 x 30 nc 5,5 30
5.5 x 30 nc 5.5 30

Could you please help me with this code? Thank you in advance. I have worked in the past with Left and right functions to the the Nth character. But I don’t know where to start with this code. I am using sql server 2019

Advertisement

Answer

Assuming that you have only one 'x' in your string (as in all your examples), then the following works:

select *
from t cross apply
     (select stuff(col1, 1, len(col1) - patindex('%[^0-9,.]%', reverse(col1) + ' ') + 1, '') as col1,
             left(col2, patindex('%[^0-9,.]%', col2 + ' ') - 1) as col2
      from (values (replace(left(t.input, charindex('x', t.input) - 1), ' ', ''),
                    replace(stuff(t.input, 1, charindex('x', t.input), ''), ' ', '')
                   ) 
           ) v(col1, col2)
     ) v;

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement