-1

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
user15132810
  • 23
  • 2
  • 1
    Does this answer your question? [Query to get only numbers from a string](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) – squillman Feb 16 '22 at 20:16
  • you may could take a look to REGEXP_SUBSTR https://way2tutorial.com/sql/sql-regexp_substr-function.php – Processor Feb 16 '22 at 20:28
  • No, i have null values in the column so that wont work.Thanks – user15132810 Feb 16 '22 at 20:28
  • @Processor This is SQL Server, not MySQL or Oracle. They're completely different products. – squillman Feb 16 '22 at 20:29
  • @user15132810 Just use that question and include `ISNULL(val, '')` to account for the NULLs in your data – squillman Feb 16 '22 at 20:30
  • The answers to your previous question show how to get numbers, they just use it to exclude them, so reverse the logic to include them. – Dale K Feb 16 '22 at 20:39

1 Answers1

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45