SQL Scalar-valued Function – Strip characters from a string by using a mask/match-expression (alpha-numeric, non-numeric, etc.)

A great addition to your SQL Cookbook!

A few examples:

Alphabetic only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^a-z’)

Numeric only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^0-9’)

Alphanumeric only:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘^a-z0-9’)

Non-alphanumeric:
SELECT dbo.fn_StripCharacters(‘a1!s2@d3#f4$’, ‘a-z0-9’)

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

/*
Alphabetic only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

Alphanumeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
*/

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

RETURN @String

END

Advertisements

2 thoughts on “SQL Scalar-valued Function – Strip characters from a string by using a mask/match-expression (alpha-numeric, non-numeric, etc.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s