Extracting digits from a alphanumeric string using single SELECT statement

Posted by Bandi under Sql Server category on | Points: 40 | Views : 574
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


Comments or Responses

Login to post response