To remove the specified Characters in the Given String

Question

To remove the specified Characters in the Given String

Ramji Sridharan

SSC Enthusiast

Points: 187
More actions
June 13, 2013 at 8:45 pm

#278576

Comments posted to this topic are about the item To remove the specified Characters in the Given String

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kricky SSC Veteran Points: 205 More actions · Answer 1

Did I miss something here? It looks like the Function is called "dbo.GEN_FN_StripCharacters", but the selects referenced in the comments are "dbo.fn_StripCharacters".

Kris Robinett

Ed.Wallander Right there with Babe Points: 787 More actions · Answer 2

Just looks like Ram called the function by an earlier name.

I made a slight modification, converting the string types that will be stripped out to a parameter rather than inputting the expression. I also added more characters to the test string just to be sure it was capturing a few more non-alphanumeric characters for my own peace of mind.

CREATE FUNCTION dbo.GEN_FN_StripCharacters

(

@strMatchType NVARCHAR(3),

@strInputString NVARCHAR(MAX)

)

/*

---Created By : Ram

--Date : 15-Feb-2013

--- Purpose : To remove the specified Characters in the Given String

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')

--Modified by : Ed

--Date : 01-Jul-2013

Converted match strings into a match type parameter

A-Alpha

N-Numeric

AN-AlphaNumeric

Precede with ^ to denote NOT

'^AN' = Not AlphaNumeric

*/

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE @strMatchExpression varchar(15)

SET @strMatchExpression = '%[' + CASE @strMatchType

WHEN 'A' THEN 'a-z'

WHEN '^A' THEN '^a-z'

WHEN 'N' THEN '0-9+-/'

WHEN '^N' THEN '^0-9+-/'

WHEN 'AN' THEN 'a-z0-9'

WHEN '^AN' THEN '^a-z0-9'

END + ']%';

WHILE

PATINDEX( @strMatchExpression , @strInputString ) > 0

SET @strInputString = STUFF( @strInputString , PATINDEX( @strMatchExpression , @strInputString ) , 1 , '' );

RETURN @strInputString;

END;

SELECT dbo.GEN_FN_StripCharacters('a','a"1!S2@{]d3#f4$`~''')

SELECT dbo.GEN_FN_StripCharacters('^a','a"1!S2@{]d3#f4$`~''')

SELECT dbo.GEN_FN_StripCharacters('n','a"1!S2@{]d3#f4$`~''')

SELECT dbo.GEN_FN_StripCharacters('^n','a"1!S2@{]d3#f4$`~''')

SELECT dbo.GEN_FN_StripCharacters('an','a"1!S2@{]d3#f4$`~''')

SELECT dbo.GEN_FN_StripCharacters('^an','a"1!S2@{]d3#f4$`~''')

tskelley SSCommitted Points: 1870 More actions · Answer 3

Great function! Definitely going into my Hall of Fame!

One other trick it handled nicely was including a decimal in the numeric sort order. For example, I needed 1.11.338 to show before 10.1.002 By adding a decimal at the end of the numeric check, I was able to sort the numbers with multiple decimals correctly.

ufn_StripCharacters([ColumnName], '^0-9+-/.')

Awesome!!!!!!!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

May 2, 2016 at 12:59 pm

#1876004

Thanks for the script.