How to get the numeric part from a string using T-SQL?

Question

I have the following string.

Input
--------------
2030031469-NAI

To get the numeric part, I am using the following script

declare @str varchar(50)= '2030031469-NAI'

Select 
    @str
    ,SUBSTRING(@str, 1,  NULLIF(CHARINDEX('-', @str) - 1, -1))
        ,Left(@str,PATINDEX('%-%',@str)-1)

to get the following output

Output: 
----------
2030031469

Is there any other easy/elegant way of doing the same?

score 39 · Accepted Answer · answered May 04 '12 at 06:11

39

select left(@str, patindex('%[^0-9]%', @str+'.') - 1)

answered May 04 '12 at 06:11

Mikael Eriksson

132,594
21
199
273

didn't work with xxx-2030031469. It will be better if it can be more generic to handle all cases. – John Li Jun 18 '20 at 13:13

score 9 · Answer 2 · answered Feb 27 '17 at 12:04

9

In case your string start with alphabet and end with number like ERT-123

you can use this query:

 (select  substring(@str,patindex('%[0-9]%', @str),len(@str)))

answered Feb 27 '17 at 12:04

Pejman Nikram

560
2
12
24

score 6 · Answer 3 · answered May 04 '12 at 06:05

6

Please check with this, i used in my project for extracting phone numbers

 CREATE Function [dbo].[RemoveNonNumericCharacters](@Temp VarChar(1000))
    Returns VarChar(1000)
    AS
    Begin

        While PatIndex('%[^0-9]%', @Temp) > 0
            Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')

        Return @TEmp
    End

answered May 04 '12 at 06:05

Maddy

3,546
9
39
54

i found in this blog http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-extracting-data-again This is really good one – Maddy May 04 '12 at 06:05

score 2 · Answer 4 · answered Aug 04 '18 at 10:33

2

To extract number from an unformatted string

DECLARE @Text NVARCHAR(100)= 'extract only 23124R integer @#%%'
SELECT SUBSTRING(@Text, PATINDEX('%[0-9]%',@Text), PATINDEX('%[^0-9]%',SUBSTRING(@Text, PATINDEX('%[0-9]%',@Text), LEN(@Text)))-1) [ONLY_INT]

answered Aug 04 '18 at 10:33

Tanmay Vaishya

41
6

this fails on null string – David Laplante Dec 18 '19 at 20:00
i think its a good practice to check variable first and then perform any kind of operation on it. Atleasst thats what I do. – Tanmay Vaishya Jun 04 '20 at 06:48

How to get the numeric part from a string using T-SQL?

4 Answers4

Linked