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 41 · Accepted Answer · answered May 04 '12 at 06:11

41

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

answered May 04 '12 at 06:11

Mikael Eriksson

136,425
22
210
281

2

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
This answer is elegant and works well, but not in general cases like mine, for instance when you have casting issues to (big)int. Then the general function which Maddy suggested is best. – Marja van der Wind Jul 17 '23 at 12:16

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

10

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

570
2
11
26

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

7

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,726
9
41
55

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

53
7

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