Print required numeric data from unstructured string

Question

Hi everyone,

Can i request you to suggest a way to print the numeric data from a text column which is not followed any format of data. (SQL Server)

Ex: Input Output
123abc ---> 123
123.98abc --> 123.98
123 xy28 --> 123
123_xy_98 -->123
123_98_ab_00 -->123
A_123_123 --> 123
A-123-123 --> 123
A%xy125_123--> 125

Something like above. Input string doesn't follow any particular format. But we always need to take the first numeric part of it (including decimals if we have). We can have underscores, - , % or dot or space or multiple times a special character is repeated.

I have written 7 different SQL case statements , but still missing few more cases and this solution is kind of hard codes one. Can we have anything like dynamically identify the special character and just display the first numeric data including the decimals places(if we have ).

Thanks in advance.

Answer 1

I added a couple of additional test cases.

Declare @Sample Table(MyData varchar(30));
Insert @Sample(MyData) Values
('123abc'), ---> 123
('123.98abc'), --> 123.98
('123 xy28'), --> 123
('123_xy_98'), -->123
('123_98_ab_00'), -->123
('A_123_123'), --> 123
('A-123-123'), --> 123
('A%xy125_123'), --> 125
('257'),  -- 257
('257ABC'),  -- 257
('abcdef') -- <empty string>;

;
;With cteFirstLoc As
(Select MyData, PatIndex('%[0-9.]%', MyData) As StartPos, Len(MyData) As DataLen
From @Sample),
cteLastLoc As
(Select MyData, StartPos, DataLen, PatIndex('%[^0-9^.]%', SubString(MyData, StartPos,  DataLen)) As EndPos
From cteFirstLoc)
Select MyData, StartPos, EndPos, 
  Case When StartPos = 0 Then ''
    When EndPos = 0 Then SubString(MyData, StartPos,  DataLen)
    Else SubString(MyData, StartPos,  EndPos - 1)
    End As Result
From cteLastLoc;

Tom