sqlsql-server

Query to get only numbers from a string


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.


Solution

  • 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 LEN(COALESCE(TRIM(CAST(ISNULL(@strAlphaNumeric, 0) AS INT)),0))>0 then COALESCE(TRIM(CAST(ISNULL(@strAlphaNumeric, 0) AS INT)),0) else 0 end
    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

    4/10/23 - Modified Return Statement based on comments