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

Table of Contents

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,.]')
comments powered by Disqus

Related Posts

Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell

I don’t know you, but people from other BU are old fashioned guys that do everything by hand, and then send you an Excel file to “put this data in the database” or “find info related to this codes”.

Read More

Row Level Security Performance Implications

A conversation had me wondering about performance implications in SQL Server RLS feature, specifically on how to mitigate the performance impact;

Read More
How to build an Application with modern Technology

How to build an Application with modern Technology

Nemo vel ad consectetur namut rutrum ex, venenatis sollicitudin urna. Aliquam erat volutpat.

Read More