How Can I Extract Numeric Values from Strings in SQL?

Extract numerical data from string

In a database environment, it may be necessary to extract embedded numeric data from a string. Consider the following example:

<code>字符串 1: 003Preliminary Examination Plan   
字符串 2: Coordination005  
字符串 3: Balance1000sheet</code>

The desired result is to extract only the numbers from each string:

<code>字符串 1: 003
字符串 2: 005
字符串 3: 1000</code>

To implement this in SQL, you can create a custom user-defined function (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>

This UDF essentially identifies and removes all non-numeric characters from the input string. Then, it checks if the resulting string can be converted to an integer and returns it accordingly.

To use this function, simply apply it to the relevant column:

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

This will return only the numeric portion of the string in the specified column. For the example data provided, the results will match the expected values:

<code>003
005
1000</code>

Note: There was a grammatical error in the RETURN statement in the original code, which has been corrected to a more concise and effective RETURN ISNULL(TRIM(@strAlphaNumeric), '0') to avoid unnecessary nesting and type conversion problems. This revised function is clearer and easier to understand and maintain.

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