Extracting digits from a alphanumeric string using single SELECT statement

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 590
We can extract the digits from a alphanumeric string by creating user-difned function to loop through each character to fetch the digits... Sometimes, we might need to do this same task in single SELECT.

This can be possible with the help of PATINDEX. In this case, we can query as follows to extract the digits,

--Sample Data 
Declare @tab table (String varchar(100))
insert @tab
SELECT 'QBR9-02-Feb' union all
SELECT 'QADFSA10-04-Apr' union all
SELECT 'OnlyString'

--query to extract digits
Select REPLACE(Left(SubString(String, PatIndex('%[0-9.-]%', String), 8000), PatIndex('%[^0-9.-]%', SubString(String, PatIndex('%[0-9.-]%', String), 8000) + 'X')-1), '-','')
FROM @tab


output:
Digits 
902
1004

Comments or Responses

Login to post response