TrimChar to Remove Desired Character

• Download source code - 370 B

Introduction

This is a small function to remove occurrences of a character from the given text. The occurrences are removed from both ends of the data. 

For example, if you have the following text...

',,some,and,data,,,'

...and you want to remove both trailing and leading commas and to get a result like this:

'some,and,data'

Using the Code

While this can be done in a SELECT statement by using only built-in functions, it's quite cumbersome to do so and you would need to repeat the same functions several times. So an easy way is to wrap the functionality into a small function like the following:

CREATE FUNCTION TrimChar(@data varchar(max), @chartotrim char) 
RETURNS varchar(max) AS
BEGIN

DECLARE 
@length int,
@trimmed varchar(max);

SET @length = LEN(@data);
SET @trimmed = REVERSE( SUBSTRING( @data, PATINDEX('%[^' + @chartotrim + ']%', @data), @length));
SET @trimmed = REVERSE( SUBSTRING(@trimmed, PATINDEX('%[^' + @chartotrim + ']%', @trimmed), @length));

RETURN (@trimmed);

END;

The logic is:

1. PATINDEX
   Find the first occurrences of any other character than the one to be removed 
   In the example: 3
2. SUBSTRING
   Get the remainder of text starting from the position defined in bullet 1
   In the example:  'some,and,data,,,'
3. REVERSE
   Reverse the string
   In the example: ',,,atad,dna,emos'
4. PATINDEX
   Find the first occurrence of any other character than the one to be removed 
   In the example: 4
5. SUBSTRING
   Get the remainder of text starting from the position defined in bullet 4
   In the example: 'atad,dna,emos'
6. REVERSE
   Reverse the text
   In the example: 'some,and,data'

So if we try to call the function, the following statement...

SELECT dbo.TrimChar(',,some,and,data,,,', ',')

...would result in:

some,and,data

Points of Interest

The pattern used with PATINDEX finds character that is something else than the character defined in the pattern. This is done using not prefix in the wildcard (^). Since this is probably something that's quite rarely used, it may be worth looking at. For more information have a look at (Wildcard - Character(s) Not to Match) (Transact-SQL).

References

The following functions were used:

• PATINDEX
• REVERSE
• SUBSTRING

History

• 26^th September, 2016: Created
• 30^th September, 2016: References added