'Get numbers from the character string

I have a column in SQL Server which contains a characters and a random numbers or a dash and random character string. I want to extract the numbers to different field. Does anyone have any suggestion?

create table stage.test(
    geo_code varchar(50) null
)
    
insert into stage.test values ('mobile-pensacola (ft walt) (686)')
insert into stage.test values ('rj-globo rio (76008)' )
insert into stage.test values ('ce2-tv (6666)' )
insert into stage.test values ('mumbai metropolitan region (356002)')
insert into stage.test values ('')

Tried this query which is not working as expected

select isnull(SUBSTRING([geo_code],CHARINDEX('(',[geo_code ])+1 ,CHARINDEX(')',[geo_code ])-CHARINDEX('(',[geo_code ])-1),'')  as [geo_code ]  as geo_code 
 from stage.test

Expected output
686
76008
6666
356022
null or blank
sqlsql-server


Solution 1:[1]

Sometimes a PATINDEX gives that extra OOMPH over a CHARINDEX

SELECT SUBSTRING([geo_code]
                 , PATINDEX('%([0-9]%',[geo_code])+1
                 , PATINDEX('%[0-9])%',[geo_code]) 
                 - PATINDEX('%([0-9]%',[geo_code])
                 ) AS geo_num
FROM test;
geo_num
686
76008
6666
356002

Test on db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LukStorms

Related Questions