How to Extract Numbers from Strings Using SQL?

Extraction of numbers from SQL strings

Question:

Write a SQL query to extract only numeric values ​​from a given string. For example, given the input strings "003Preliminary Examination Plan", "Coordination005" and "Balance1000sheet", the desired outputs are "003", "005" and "1000" respectively.

Solution:

To accomplish this task, we can leverage user-defined functions (UDFs) to parse the string and retrieve the numeric value. Here’s how to do it:

<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), '')
END
GO</code>

Now we can use this UDF in our query:

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

This advanced method efficiently extracts numeric values ​​from strings in SQL. Note that this modified UDF handles null values ​​and cases containing only non-numeric characters, returning an empty string instead of 0.

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