How to Get the Numeric Part from a String Using T-Sql

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

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

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.

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

How to extract numbers from a string using TSQL

Since you have stable text and only 2 elements, you can make good use of replace and parsename:

declare @string varchar(100) = 'TEST RESULTS\TEST 1\RESULT 2'

select cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 2) as int) as Test
    , cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 1) as int) as Result

/*
       Test      Result
----------- -----------
          1           2
*/

The replace portion does assume the same text and spacing always, and sets up for parsename with the period.

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

Finding out just the numeric part from string and storing it as decimal datatype

If there can be only one such number in a string and if it always starts with a digit (i.e. there are no values omitting the 0 before the decimal like '.75') and ends with a digit, you can find the first digit by applying patindex() to the string (as you already do) and the last digit by applying patindex() to the reverse() of the string.

SELECT convert(decimal(3, 1),
               substring(column_desc,
                         patindex('%[0-9]%',
                                  column_desc),
                         len(column_desc)
                         - patindex('%[0-9]%',
                                    column_desc)
                         - patindex('%[0-9]%',
                                    reverse(column_desc))
                         + 2)) new_value
FROM elbat;

db<>fiddle

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.

---

Related Topics