Extract Numbers from a Text in SQL Server

Query to get only numbers from a string

First create this UDF

CREATE FUNCTION dbo.udf_GetNumeric
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
  END
  RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Now use the function as

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

I hope this solved your problem.

Reference

SQL take just the numeric values from a varchar

Here's the example with PATINDEX:

select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))

This assumes (1) the field WILL have a numeric, (2) the numerics are all grouped together, and (3) the numerics don't have any subsequent characters after them.

Extract numbers from a text in SQL Server

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)

SQL: Extract numbers from string using Patindex-function

According to your sample data and expected results, you can get it with some relatively simple expressions.

SELECT A,
    SUBSTRING( A, 0, ISNULL(NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A)+1)),
    SUBSTRING( A, NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A))
FROM  #Test;

For the first column, I start with 0 to avoid substracting 1 to the first digit position. I then use a combination of ISNULL(NULLIF(,0)LEN(A)) to assign the total length in case there are no digits available.

For the second columns, I still use NULLIF, but I don't use ISNULL to keep the value as NULL. I then just add the total length of the string.

How to extract numbers(Integer) from String field in database and find the maximum

You may use the following JPQL query:

SELECT
    MAX(CAST(SUBSTRING(id, 1, LOCATE(id, '-') - 1) AS INTEGER))
FROM EmployeeTripCard s;

We can use LOCATE to find the index of the first -, then call SUBSTRING to find the initial number. Note carefully that we also need to cast this resulting string to an integer, in order for MAX to behave the way we want (numbers as text don't always sort the same way as actual pure numbers).

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

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

T-SQL Extract Numbers from a string and everything in between

You can use charindex() with patindex():

select substring(col, patindex('%[0-9]%', col), 
                      len(col) - charindex(' ', col, patindex('%[0-9]%', col)) - 1
                ) as col

SQL How to extract numbers from a string?

Use a combination of Substr & instr

SELECT Substr (textstring, 1,instr(textstring,' ') - 1) AS Output
FROM yourtable

Result:

OUTPUT
666
12345

Use this if you have text at the beginning e.g. aa12345 devils number is my PIN, that is 6666. as it utilises the REGEXP_REPLACE function.

SELECT REGEXP_REPLACE(Substr (textstring, 1,instr(textstring,' ') - 1), '[[:alpha:]]','') AS Output
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!4/8edc9/1/0

---

Related Topics