Query to get only numbers from a string

Question

I have data like this:

string 1: 003Preliminary Examination Plan    string 2: Coordination005   string 3: Balance1000sheet 

The output I expect is

string 1: 003 string 2: 005 string 3: 1000 

And I want to implement it in SQL.

Answer 1

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

Answer 2

Try this one -

Query:

DECLARE @temp TABLE (       string NVARCHAR(50) )  INSERT INTO @temp (string) VALUES      ('003Preliminary Examination Plan'),     ('Coordination005'),     ('Balance1000sheet')  SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)  FROM (     SELECT subsrt = SUBSTRING(string, pos, LEN(string))     FROM (         SELECT string, pos = PATINDEX('%[0-9]%', string)         FROM @temp     ) d ) t 

Output:

---------- 003 005 1000