Extract numbers from a text in SQL Server

Question

i was searching script to extract number from text in sql server and i found this

CREATE FUNCTION [dbo].[GetNumbersFromText](@String VARCHAR(2000))
RETURNS @Number TABLE (Number INT)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
--Find a numeric charactor
IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
--If the next charactor is not a numeric one, the current number ends, so add a    separator
IF (SUBSTRING(@String,@Count+1,1) < '0'OR SUBSTRING(@String,@Count+1,1) > '9') AND    SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + ','
END
SET @Count = @Count + 1
END
---Split string to give a table with the numbers in the text
INSERT INTO @Number
SELECT DISTINCT items FROM dbo.Split(@IntNumbers, ',')
return
END

and call it like

SELECT Number FROM Dbo.[GetNumbersFromText]('Give me 120 this week and 50 next week')

it works fine but i need more short code. can i use patindex to extract number from text. please anyone share small & good logic to do so. thanks

Why do you need "shorter code" and "small logic"? If your function works correctly and performs acceptably then there is no reason at all to change it. — Pondlife, Mar 09 '12 at 08:55

Mikael Eriksson · Accepted Answer · 2012-03-09T07:11:50.570

This is a bit shorter. Turned it into Inline Table Function that uses a recursive CTE to find the numbers.

create function [dbo].[GetNumbersFromText](@String varchar(2000))
returns table as return
(
  with C as
  (
    select cast(substring(S.Value, S1.Pos, S2.L) as int) as Number,
           stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
    from (select @String+' ') as S(Value)
      cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    union all
    select cast(substring(S.Value, S1.Pos, S2.L) as int),
           stuff(S.Value, 1, S1.Pos + S2.L, '')
    from C as S
      cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    where patindex('%[0-9]%', S.Value) > 0
  )
  select Number
  from C
)

If you expect to have more than 100 numbers in the string you need to call it with option (maxrecursion 0).

declare @S varchar(max)
set @S = 'Give me 120 this week and 50 next week'
select number from GetNumbersFromText(@S) option (maxrecursion 0)

score 3 · Answer 2 · answered Mar 09 '12 at 08:20

@Vikram's basic idea is not bad, but their query would return all the numbers as a single item. The following function returns a table containing separate numbers as found in the source string:

CREATE FUNCTION dbo.GetNumbersFromText (@String varchar(2000))
RETURNS TABLE
AS
RETURN (
  WITH NumbersSplit AS (
    SELECT
      C = SUBSTRING(@String, number, 1),
      i = number,
      g = number - ROW_NUMBER() OVER (ORDER BY number)
    FROM master..spt_values
    WHERE type = 'P'
      AND SUBSTRING(@String, number, 1) BETWEEN '0' AND '9'
  ),
  NumbersAssembled AS (
    SELECT
      number = CAST(
        (SELECT C + '' FROM NumbersSplit WHERE g = g.g ORDER BY i FOR XML PATH (''))
        AS varchar(2000)
      )
    FROM NumbersSplit g
    GROUP BY g
  )
  SELECT * FROM NumbersAssembled
)

Note: this solution would work in SQL Server 2005 or later.

score 2 · Answer 3 · answered Mar 09 '12 at 07:34

try the following logic:

declare @thestring varchar(50) 
set @thestring = 'Give me 120 this week and 50 next week' 
declare @final varchar(50) 
set @final = '' 

select @final = @final + x.thenum 
from 
( 
    select substring(@thestring, number, 1) as thenum, number 
    from master..spt_values 
    where substring(@thestring, number, 1) like '[0-9]' and type='P'
) x 
order by x.number 

print @final

Mehdi Hassani Goodarzi · Answer 4 · 2018-02-28T17:00:33.710

-- Try This Code... -- Its OK!!!

CREATE FUNCTION [dbo].[udf_ExtractNumberFromString]
(
    @pInputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @OutputString varchar(MAX)=''
    DECLARE @string varchar(MAX)
    DECLARE @start INT
    DECLARE @end INT
    DECLARE @len INT

    SET @string=@pInputString
    --SET @string = 'the22478ddffafghrty12345TestAddressdd5aa789324-#345'
    SET @string = replace(@string, ' ' , '')

    WHILE PATINDEX('%[0-9]%',@string) <> 0
    BEGIN   
        SET @len = len(@string)
    --  PRINT @len

        set @start = PATINDEX('%[0-9]%',@string)
    --  PRINT @start

        SET @end= PATINDEX('%[^0-9]%',SUBSTRING(@string,@start,@len-@start))
    --  PRINT @end

        IF @end=0
            BEGIN
                SET @end=@len-@start
                SET @OutputString=SUBSTRING(@string,@start,@end+1)+'-'+@OutputString
                BREAK
            END
        ELSE 
            BEGIN 
                SET @OutputString=SUBSTRING(@string,@start,@end-1)+'-'+@OutputString
                SET @string=SUBSTRING(@string,@end+@start-1,@len-@end)
            END 

        --PRINT @string

        --PRINT @Output
        --PRINT '---------------------'
    END

    IF LEN(@OutputString)>0
        SET @OutputString=LEFT(@OutputString,LEN(@OutputString)-1) 
    --PRINT @OutputString

    RETURN @OutputString
END

score -2 · Answer 5 · answered Nov 20 '14 at 10:46

-2

Here is an appropriate and working solution for this query

http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

answered Nov 20 '14 at 10:46

Imtiyaz

11
1

Welcome to Stack Overflow! Whilst this may theoretically answer the question, [it would be preferable](http://meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Bowdzone Nov 20 '14 at 11:07

Extract numbers from a text in SQL Server

5 Answers5

Linked