How to extract numeric values from a string in SQL Server

Question

I have a string in a SQL Server table whose format is like this...

nvarchar int nvarchar int nvarchar

There are no obvious delimiters other than some chars are numeric and others are alpha.

How do I reference the second int value?

Answer 1

One way is to use the patindex function:

declare @s varchar(100)
declare @i1 int
declare @s2 varchar(100)
declare @i2 int
declare @s3 varchar(100)
declare @i3 int
declare @s4 varchar(100)
declare @i4 int
declare @secondInt int

set @s = 'alpha123beta3140gamma789'

set @i1 = PATINDEX('%[0-9]%', @s)
set @s2 = SUBSTRING(@s, @i1, 100)
set @i2 = PATINDEX('%[^0-9]%', @s2)
set @s3 = SUBSTRING(@s2, @i2, 100)
set @i3 = PATINDEX('%[0-9]%', @s3)
set @s4 = SUBSTRING(@s3, @i3, 100)
set @i4 = PATINDEX('%[^0-9]%', @s4)

set @secondInt = CAST(SUBSTRING(@s4, 1, @i4-1) as int)

select @s, @secondInt

Answer 2

This article on using Regular Expressions with SQL Server may be helpful.

Regular Expressions Make Pattern Matching And Data Extraction Easier

Answer 3

I would personally write a CLR function and use the string SPLIT function. Here is some code that I believe works:

Declare @Result Table
(
    stringval varchar(100),
    numvalue decimal(18,4)
)

Declare @Test  varchar(100)
Declare @index int
Declare @char char(1)
Declare @currentVal varchar(100)
Declare @prevVal varchar(100)
Declare @currentType char(1)
Declare @nextType char(1)

Set @index = 0
Set @Test = 'a100.4bb110ccc2000'

Set @currentVal = ''
Set @currentType = 's'

While @index <= LEN(@Test)
Begin
    Set @index = @index + 1
    Set @char = SUBSTRING(@Test,@index,1)       

    Set @nextType = CASE WHEN PATINDEX('[^0-9.]', @char) > 0 then 's' else 'n' end

    If @currentType <> @nextType 
    begin
        if @currentType = 'n'
            insert into @Result(stringval,numvalue) values(@prevVal,@currentVal)
        Set @prevVal = @currentVal
        set @currentVal = ''
        set @currentType = @nextType
    end

    SEt @currentVal = @currentVal + @char


ENd

Select * FROM @Result