Site icon Facebook Portrait Project

How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call

I am using the following SQL to create a function to strip out characters or numbers.

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

I am calling it like this to extract the alpha characters and then the numeric characters into two sort fields:

SELECT
    ...
    (SELECT dbo.fn_StripCharacters(PD.District, '^a-z')) AS Sort1,
    CAST((SELECT dbo.fn_StripCharacters(PD.District, '^0-9')) AS INT) AS Sort2,
    ...
FROM

I am searching a LOT of Postcode District Records and the constant calls to the function are causing a comparatively big delay. Is there a way of reproducing this functionality without a function call? Can it be incorporated into the SELECT statement somehow?

SQL Server Version is 18.9.2

Exit mobile version