Avinash Mehta Avinash Mehta - 3 months ago 19
SQL Question

Query to get only numbers from a string

suppose 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.
Please help.
Thanks in advance :)

Luv Luv
Answer

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