I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Common].[AllowedCharacters] ([Character],[Replacement],[AlwaysInclude])
VALUES ('A',' ',1),
('B',' ',1),
('C',' ',1),
('D',' ',1),
('E',' ',1),
('F',' ',1),
('G',' ',1),
('H',' ',1),
('I',' ',1),
('J',' ',1),
('K',' ',1),
('L',' ',1),
('M',' ',1),
('N',' ',1),
('O',' ',1),
('P',' ',1),
('Q',' ',1),
('R',' ',1),
('S',' ',1),
('T',' ',1),
('U',' ',1),
('V',' ',1),
('W',' ',1),
('X',' ',1),
('Y',' ',1),
('Z',' ',1),
('.',' ',1),
('-',' ',1),
('/',' ',1),
('&',' ',1)
Test data –
Create Table Customerstrings( UserName varchar(30),[Password] varchar(30);
INSERT INTO Customerstrings
(
UserName
,[Password]
)
VALUES
('Bill123', 'Jones12.z')
,('John&12/', 'QWERT123&4')
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
Thank you for any help!
August 10, 2015 at 6:10 am
I've done it the opposite way, replace NOT allowed characters.
if a char is not allowed, do you just remove it, or replace it with a space or other char?
here's an ITVF function and example.
you can expand it to allow other, specific values, or handle the replace, if desired.
/*--Results
For example - Forexample
Bill123? Bill123
Jones12.z- Jones12z
John&12/ John12
QWERT123&4 QWERT1234
*/
;WITH MyCTE([TheString])
AS
(
SELECT 'For example -' UNION ALL
SELECT 'Bill123?' UNION ALL
SELECT 'Jones12.z-' UNION ALL
SELECT 'John&12/' UNION ALL
SELECT 'QWERT123&4'
)
SELECT * FROM MyCTE
CROSS APPLY dbo.StripNonAlphaNumeric_ITVF([TheString])
ALTER FUNCTION dbo.StripNonAlphaNumeric_itvf
(
@OriginalText Varchar(8000)
)
RETURNS TABLE
AS
RETURN
(
WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
,DATA as
(select N,substring(@OriginalText,N,1) as element
from tally
--ascii numbers are 48(for '0') thru 57 (for '9')
where ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57
--ascii upper case letters A-Z is 65 thru 90
OR ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 65 AND 90
--ascii lower case letters a-z is 97 thru 122
OR ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 97 AND 122
and N <= len(@OriginalText)
)
Select(Select '' + element
FROM data
ORDER BY N
FOR XML PATH('')
) as [NumberString]
)
GO
Lowell
August 10, 2015 at 7:30 am
Any reason not use LIKE?
ALTER FUNCTION dbo.StripNonAlphaNumeric_itvf
(
@OriginalText Varchar(8000)
)
RETURNS TABLE
AS
RETURN
(
WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
,DATA as
(select N,substring(@OriginalText,N,1) as element
from tally
--ascii numbers are 48(for '0') thru 57 (for '9')
where SUBSTRING(@OriginalText,Tally.N,1) LIKE '[0-9A-Za-z]' COLLATE LATIN1_GENERAL_BIN
and N <= len(@OriginalText)
)
Select(Select '' + element
FROM data
ORDER BY N
FOR XML PATH('')
) as [NumberString]
)
GO
EDIT: Added collation
August 10, 2015 at 7:40 am
Hi Lowell,
Thank you for the help but the requirements need the characters to be invalidated against a table containing characters allowed and characters that are not contained within the table are replaced with a space.
The table with the allowed characters will be then updated by users.
I'm not sure how to do a character search against table using the example sql data.
Any help greatly appreciated.
Thanks
August 10, 2015 at 7:42 am
Luis Cazares (8/10/2015)
Any reason not use LIKE?
yes, there's a reason!
i built this a while ago , and i have a bunch of variations of it for specific purtposes.
but i remember that i got false matches due to collation and case sensitive issues, unless i specified the ascii code , in one isntnace, i need only A-Z, not the and not characters with flair(accents,oomlats, etc?)
SELECT * FROM dbo.StripNonAlphaNumeric_itvf('ÀAlbèert ËEîinstêeiìn ÌInstìitúutëe')
Lowell
August 10, 2015 at 7:52 am
Lowell (8/10/2015)
Luis Cazares (8/10/2015)
Any reason not use LIKE?yes, there's a reason!
i built this a while ago , and i have a bunch of variations of it for specific purtposes.
but i remember that i got false matches due to collation and case sensitive issues, unless i specified the ascii code , in one isntnace, i need only A-Z, not the and not characters with flair(accents,oomlats, etc?)
SELECT * FROM dbo.StripNonAlphaNumeric_itvf('ÀAlbèert ËEîinstêeiìn ÌInstìitúutëe')
Using the binary collation would prevent that. 😉
August 10, 2015 at 8:07 am
This should work as explained in the original post. It doesn't return the same results because you don't have digits in your AllowedCharacters table.
CREATE FUNCTION dbo.StripNonAllowedChars
(
@OriginalText Varchar(8000)
)
RETURNS TABLE
AS
RETURN
(
WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
,DATA as
(select N, CASE WHEN SUBSTRING(@OriginalText,Tally.N,1) IN (Select [Character]
FROM [AllowedCharacters]
WHERE [AlwaysInclude] = 1)
THEN substring(@OriginalText,N,1) ELSE ' ' END as element
from tally
--ascii numbers are 48(for '0') thru 57 (for '9')
WHERE N <= len(@OriginalText)
)
Select(Select '' + element
FROM data
ORDER BY N
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)') as [String]
)
GO
August 10, 2015 at 9:11 am
This is completely different, using a msTVF vs an iTVF but doesn't have the overhead of the XML black box:
CREATE FUNCTION fn_RemoveUnwantedCharacters
(@MyString VARCHAR(50)) -- arbitrary max, matches iTally table variable
RETURNS @MyTable TABLE (MyString VARCHAR(50))
AS
BEGIN
-- this could be external to the function
DECLARE @Includes TABLE (IncludedChar CHAR(1) PRIMARY KEY)
INSERT INTO @Includes (IncludedChar) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
-- so could this
DECLARE @iTally TABLE (n INT PRIMARY KEY)
INSERT INTO @iTally VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),
(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)
-- this is just a variable assignment
SELECT @MyString = STUFF(@MyString,d.n,1,ISNULL(i.IncludedChar,' '))
FROM @iTally d
LEFT JOIN @Includes i ON SUBSTRING(@MyString,d.n,1) = i.IncludedChar
WHERE d.n <= LEN(@MyString)
ORDER BY d.n
-- Fill the table variable with the rows for your result set
INSERT INTO @MyTable (MyString) SELECT @MyString
RETURN
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 10:52 am
Thanks,
how do you call the function in a select?
August 10, 2015 at 10:56 am
Something like this as they're table valued functions:
SELECT UserName,
Password,
u.String AS CleanUserName,
p.String AS CleanPassword
FROM Customerstrings
CROSS APPLY dbo.StripNonAllowedChars( UserName) u
CROSS APPLY dbo.StripNonAllowedChars( Password) p
August 10, 2015 at 11:03 am
Thanks, will try out later on tonight.
Have a great night.
August 11, 2015 at 9:47 am
Hi ChrisM,
Your solution is perfect but it changes the case from lower / title to upper case can this be corrected to keep the original case?
Thanks
azdeji (8/11/2015)
Hi ChrisM,Your solution is perfect but it changes the case from lower / title to upper case can this be corrected to keep the original case?
Thanks
Sure. Sub in either one of these two replacement blocks for the variable assignment:
-- this is just a variable assignment
SELECT @MyString = STUFF(@MyString, d.n, 1, ' ')
FROM @iTally d
LEFT JOIN @Includes i ON SUBSTRING(@MyString,d.n,1) = i.IncludedChar
WHERE d.n <= LEN(@MyString)
AND i.IncludedChar IS NULL
ORDER BY d.n
-- this is just a variable assignment
SELECT @MyString = STUFF(@MyString, d.n, 1, ' ')
FROM @iTally d
WHERE d.n <= LEN(@MyString)
AND NOT EXISTS (SELECT 1 FROM @Includes i WHERE SUBSTRING(@MyString,d.n,1) = i.IncludedChar)
ORDER BY d.n
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2015 at 2:26 am
Hi Chris,
thanks you save the day, can the allowed values be stored in the function instead to make it a Scalar Function instead?
Thanks,
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply