How Can I Extract Numbers from Alphanumeric Strings in SQL?

Extract numbers from alphanumeric string in SQL

Question:

You have some strings containing alphanumeric characters and you need to extract only the numeric values. For example, given the following string:

• 003Preliminary Examination Plan
• Coordination005
• Balance1000sheet

You wish to search for:

• 003
• 005
• 1000

Solution:

To extract numbers from a string using SQL, you can use user-defined functions (UDFs). Create the following UDF:

<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
GO</code>

Usage:

After creating the UDF, you can use it to extract numbers from a string:

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

For example:

<code class="language-sql">SELECT dbo.udf_GetNumeric('003Preliminary Examination Plan')</code>

Reference:

• Custom user-defined function

Note:

This solution outputs numbers as strings. If you need integers, you can use the CAST() function to convert them. The above code has been simplified to avoid unnecessary complex logic and ensure that the function always returns a value.

The above is the detailed content of How Can I Extract Numbers from Alphanumeric Strings in SQL?. For more information, please follow other related articles on the PHP Chinese website!