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