June 13, 2013 at 8:45 pm
Comments posted to this topic are about the item To remove the specified Characters in the Given String
July 1, 2013 at 7:16 am
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
July 1, 2013 at 12:48 pm
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$`~''')
July 11, 2013 at 9:33 am
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!!!!!!!
May 2, 2016 at 12:59 pm
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply