· 8 years ago · Jun 08, 2018, 05:32 PM
1Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
2Returns VarChar(1000)
3AS
4Begin
5
6 While PatIndex('%[^a-z]%', @Temp) > 0
7 Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')
8
9 Return @TEmp
10End
11
12Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')
13
14CREATE FUNCTION [dbo].[fn_StripCharacters]
15(
16 @String NVARCHAR(MAX),
17 @MatchExpression VARCHAR(255)
18)
19RETURNS NVARCHAR(MAX)
20AS
21BEGIN
22 SET @MatchExpression = '%['+@MatchExpression+']%'
23
24 WHILE PatIndex(@MatchExpression, @String) > 0
25 SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
26
27 RETURN @String
28
29END
30
31SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
32
33SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')
34
35SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
36
37SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
38
39CREATE FUNCTION dbo.AlphaOnly (
40 @String varchar(100)
41)
42RETURNS varchar(100)
43AS BEGIN
44 RETURN (
45 REPLACE(
46 REPLACE(
47 REPLACE(
48 REPLACE(
49 REPLACE(
50 REPLACE(
51 REPLACE(
52 REPLACE(
53 REPLACE(
54 REPLACE(
55 @String,
56 '9', ''),
57 '8', ''),
58 '7', ''),
59 '6', ''),
60 '5', ''),
61 '4', ''),
62 '3', ''),
63 '2', ''),
64 '1', ''),
65 '0', '')
66 )
67END
68GO
69
70-- ==================
71DECLARE @t TABLE (
72 ColID int,
73 ColString varchar(50)
74)
75
76INSERT INTO @t VALUES (1, 'abc1234567890')
77
78SELECT ColID, ColString, dbo.AlphaOnly(ColString)
79FROM @t
80
81ColID ColString
82----- ------------- ---
83 1 abc1234567890 abc
84
85-- ============================================
86-- Create a table of blacklist characters
87-- ============================================
88IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.CharacterBlacklist'))
89 DROP TABLE dbo.CharacterBlacklist
90GO
91CREATE TABLE dbo.CharacterBlacklist (
92 CharID int IDENTITY,
93 DisallowedCharacter nchar(1) NOT NULL
94)
95GO
96INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'0')
97INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'1')
98INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'2')
99INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'3')
100INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'4')
101INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'5')
102INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'6')
103INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'7')
104INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'8')
105INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'9')
106GO
107
108-- ====================================
109IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.StripBlacklistCharacters'))
110 DROP FUNCTION dbo.StripBlacklistCharacters
111GO
112CREATE FUNCTION dbo.StripBlacklistCharacters (
113 @String nvarchar(100)
114)
115RETURNS varchar(100)
116AS BEGIN
117 DECLARE @blacklistCt int
118 DECLARE @ct int
119 DECLARE @c nchar(1)
120
121 SELECT @blacklistCt = COUNT(*) FROM dbo.CharacterBlacklist
122
123 SET @ct = 0
124 WHILE @ct < @blacklistCt BEGIN
125 SET @ct = @ct + 1
126
127 SELECT @String = REPLACE(@String, DisallowedCharacter, N'')
128 FROM dbo.CharacterBlacklist
129 WHERE CharID = @ct
130 END
131
132 RETURN (@String)
133END
134GO
135
136-- ====================================
137DECLARE @s nvarchar(24)
138SET @s = N'abc1234def5678ghi90jkl'
139
140SELECT
141 @s AS OriginalString,
142 dbo.StripBlacklistCharacters(@s) AS ResultString
143
144OriginalString ResultString
145------------------------ ------------
146abc1234def5678ghi90jkl abcdefghijkl