A quick function to remove (or keep only) string patterns from SQL Server Strings

Working with strings in SQL Server isn’t as easy or powerful as using any programming language, but, I’m sure that more than once you had to to some data manipulation directly in the database, this may help you out, and it doesn’t use Loops, CLR or XML

The idea is to exploit set logic, as opposed to the classic program logic of when you’re usually playing with strings in whenever programming language of choice:

1. Traspose the input string as a table
2. Delete what you don’t need using a predicate
3. Retraspose the results into a string

To accomplish step 1, I’ve used a similar logic as the majestic “8K splitter” by
Jeff Moden (of which you can find an amazing article here)

CREATE FUNCTION fn_TrasposeString (@String NVARCHAR(255))
RETURNS TABLE
AS RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                                    
       E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), 
       E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), 
 cteTally(N) AS (SELECT TOP (ISNULL(LEN(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
                )
SELECT SUBSTRING(@String,N,1) CharValue, N as Position
FROM cteTally

I’ve limited the String lenght to 255 characters for my use case, it can go further, but longer the string, worse the performance, obviously.

Next off, we’re just using the above and returning the string with nonmatching characters:

CREATE OR ALTER FUNCTION fn_RemoveFromString(@SourceString  NVARCHAR(255),
                                   @RemovePattern NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
     BEGIN

         IF @SourceString IS NULL
            RETURN NULL

         DECLARE @ReturnString NVARCHAR(255)= '';

         SELECT @ReturnString+=[CharValue]
         FROM   fn_TrasposeString(@SourceString)
         WHERE  [CharValue] NOT LIKE @RemovePattern
         ORDER BY [Position];
         RETURN @ReturnString;
     END;
GO

--Examples
SELECT [dbo].fn_RemoveFromString('T-SQL.Tech','[-]')
SELECT [dbo].fn_RemoveFromString('05050T25020S5050550Q550050L015.151T505056e3c0195051109h50','[0-9]')

Or matching characters

CREATE OR ALTER FUNCTION fn_KeepInString(@SourceString NVARCHAR(255),
                               @KeepPattern  NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
     BEGIN
         
         IF @SourceString IS NULL
            RETURN NULL

         DECLARE @ReturnString NVARCHAR(255)= '';

         SELECT @ReturnString+=[CharValue]
         FROM   fn_TrasposeString(@SourceString)
         WHERE  [CharValue] LIKE @KeepPattern
         ORDER BY [Position];

         RETURN @ReturnString;

     END;
GO

--Examples
SELECT [dbo].fn_KeepInString('T-SQL.Tech','[a-z,A-Z,.]')
SELECT [dbo].fn_KeepInString('05050T25020S5050550Q550050L015.151T505056e3c0195051109h50','[A-Z,a-z,.]')