How Can I Extract Numbers from Strings Using a SQL User-Defined Function?

Extracting Numeric Data from Strings in SQL Using a User-Defined Function

This guide demonstrates how to create a SQL user-defined function (UDF) to efficiently extract only numeric characters from a string. This is a common task in data cleaning and manipulation.

Creating the UDF:

The following SQL code defines a UDF named dbo.udf_GetNumeric that takes a string as input and returns the extracted numeric portion:

<code class="language-sql">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(TRIM(@strAlphaNumeric), '0')
END</code>

Applying the UDF:

Once the UDF is created, use the following SELECT statement to apply it to a column in your table:

<code class="language-sql">SELECT dbo.udf_GetNumeric(column_name) AS ExtractedNumber
FROM table_name;</code>

Replace column_name with the actual name of the column containing the strings and table_name with your table's name. The result will be a new column, ExtractedNumber, containing only the numeric values.

Illustrative Example:

Consider this sample data:

String 1	String 2	String 3
003Preliminary Examination Plan	Coordination005	Balance1000sheet

After applying the UDF, the output will be:

String 1	String 2	String 3
003	005	1000

Function Explanation:

The dbo.udf_GetNumeric function uses PATINDEX to locate the first non-numeric character. The STUFF function then removes that character. This process repeats until all non-numeric characters are eliminated, leaving only the numbers. The ISNULL and TRIM functions handle potential null values and leading/trailing spaces.

Further Enhancements (Future Update): A more detailed explanation and potential improvements to this function will be added in a future update.

The above is the detailed content of How Can I Extract Numbers from Strings Using a SQL User-Defined Function?. For more information, please follow other related articles on the PHP Chinese website!