How Can I Extract Numbers from Strings in SQL?

Extract numerical data from SQL string

In SQL, to retrieve only numeric characters from a given string, we can use User Defined Functions (UDF). Let’s dive into the steps involved:

First, we define a UDF named dbo.udf_GetNumeric, which accepts a string parameter @strAlphaNumeric.

UDF is initialized by declaring a variable @intAlpha that determines the position of the first non-numeric character in the string.

Using a WHILE loop, we iteratively remove non-numeric characters from the string until only digits remain.

Finally, we check if the resulting string contains any numeric characters. If it is, we convert it to an integer and return it; otherwise, we return 0.

To execute this function, use the following syntax:

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

For example, consider the following input string:

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

The expected output is:

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

By implementing this UDF, we can efficiently extract numeric data from strings in SQL, making it easier to use numeric values ​​in queries.

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