Multiple Methods for Extracting Pure Numeric Data in SQL Server: A Comprehensive Analysis

Problem Background and Challenges

In database management practice, scenarios frequently arise where data columns containing mixed characters require cleaning. The example data provided by users demonstrates typical dirty data formats: AB ABCDE # 123, ABCDE# 123, AB: ABC# 123, etc. The common characteristic of these data entries is that they all contain the # symbol as a prefix identifier for the numeric portion, but the length and composition of the prefix characters vary significantly.

Core Solution Based on PATINDEX and SUBSTRING

According to the highest-rated answer, we can use a combination of SQL Server's built-in string functions to achieve numeric extraction. The core idea of this method is to locate the starting and ending positions of the numeric sequence through two PATINDEX calls.

First, let's analyze the code implementation principle:

DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

The execution flow of this solution can be divided into several key steps:

The first step uses PATINDEX('%[0-9.-]%', @textval) to locate the position of the first numeric character (or decimal point, negative sign) in the string. The regular expression pattern %[0-9.-]% matches any numeric character (0-9), decimal point, or negative sign.

The second step uses SUBSTRING(@textval, PATINDEX(...), 8000) to extract the remaining string starting from the numeric position, where 8000 is a sufficiently large value to ensure extraction to the end of the string.

The third step adds the character 'X' to the end of the extracted substring, then uses PATINDEX('%[^0-9.-]%', ...) to find the position of the first non-numeric character. The pattern %[^0-9.-]% matches any character that is not a number, decimal point, or negative sign.

Finally, LEFT(..., PATINDEX(...)-1) is used to extract from the beginning of the substring to the position before the first non-numeric character, thus obtaining the pure numeric sequence.

Comparative Analysis of Alternative Solutions

TRANSLATE Function Solution

For SQL Server 2017 and later versions, the TRANSLATE function provides a more concise solution:

SELECT REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

This method first converts all non-numeric characters to a unified placeholder (such as @), then removes all placeholders at once. The advantage is concise and readable code, but it requires prior knowledge of all possible non-numeric characters and may not be flexible enough when dealing with large sets of different characters.

STUFF Function Solution

Another concise solution uses the STUFF function:

STUFF(Col, 1, PATINDEX('%[0-9]%', Col)-1, '')

This method directly removes all characters before the numeric sequence, suitable for situations where numbers appear at the end of the string and there are no other numbers before them. However, for complex strings containing multiple numeric segments, this method may not correctly extract the target numbers.

User-Defined Function Solution

For scenarios requiring repeated use, a user-defined function can be created:

CREATE FUNCTION [dbo].[StripNonNumerics](@Temp varchar(255))
RETURNS varchar(255)
AS
BEGIN
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
    Return @Temp
END

This function iterates through the string, removing non-numeric characters one by one. While the logic is clear, loop operations may impact performance when processing large amounts of data.

CTE Recursive Query Solution

For complex formats containing numeric separators, CTE and XML path can be used:

;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
data as (
    select DirtyCol, Col
    from @table
        cross apply (
            select (select C + ''
            from (select N, substring(DirtyCol, N, 1) C from tally where N<=datalength(DirtyCol)) [1]
            where C between '0' and '9'
            order by N
            for xml path(''))
        ) p (Col)
    where p.Col is not NULL
)
select DirtyCol, cast(Col as int) IntCol
from data

This method can handle numeric strings containing thousand separators and other complex formats, but the code complexity is higher, making it suitable for special format requirements.

Performance Optimization and Practical Recommendations

In practical applications, selecting which solution to use requires consideration of multiple factors:

Data Characteristic Analysis: First analyze the regularity of the data. If all data follows the pattern of # followed by numbers, the PATINDEX-based solution is most efficient. If data formats are杂乱无章, more general solutions may be needed.

Performance Considerations: For processing large data volumes, loops and user-defined functions should be avoided. Inline string function combinations typically offer better performance.

Error Handling: In practical applications, boundary conditions need to be considered, such as strings containing no numbers, abnormal numeric formats, etc., with appropriate error handling logic added.

Cross-Platform Compatibility: If database migration is considered, attention should be paid to differences in string function support across different database systems.

Comparison with Other Tools

The Power Query solution mentioned in the reference article provides another perspective:

Text.Select([DT Number],{"0".."9"})

This method uses the Text.Select function to directly select numeric characters, with more intuitive syntax. However, in the SQL Server environment, we need to use T-SQL's string functions to achieve similar functionality.

Another Power Query solution uses character range filtering:

Text.Remove(
[DT Number],
{
Character.FromNumber(32) .. Character.FromNumber(47),
Character.FromNumber(58) .. Character.FromNumber(255)
}
)

This method removes non-numeric characters based on ASCII code ranges, similar to the TRANSLATE solution in SQL Server but with different implementation mechanisms.

Extended Practical Application Scenarios

The techniques discussed in this article are not only applicable to simple numeric extraction but can also be extended to more complex data cleaning scenarios:

Phone Number Formatting: Extracting pure numbers from phone numbers containing country codes and separators.

Product Code Processing: Handling numeric product codes containing alphabetical prefixes.

Financial Data Cleaning: Processing amount data containing currency symbols and thousand separators.

By flexibly combining different string functions, various complex data cleaning requirements can be addressed, improving data quality and processing efficiency.