T-SQL query and results for filtering types of characters
-- The results below pertain to the following DBA.StackExchange answer:
-- Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters? ( https://dba.stackexchange.com/a/139132/30859 )
-- Please note that the RegEx function, SQL#.RegEx_IsMatch, is not a SQL Server built-in function. It is a
-- SQLCLR function that comes with the Free version of the SQL# library (which I created), available at:
-- https://SQLsharp.com
-- Unicode Category info found at:
-- https://www.regular-expressions.info/unicode.html#category
;WITH nums AS
(
SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS [DecimalValue]
FROM [master].[sys].[all_objects]
)
SELECT CONVERT(CHAR(3), nm.DecimalValue) + SPACE(8) AS [DecimalValue],
CHAR(nm.[DecimalValue]) + SPACE(12) AS [Character],
CASE WHEN CHAR(nm.[DecimalValue]) LIKE '%[^a-z0-9]%'
THEN 'x' ELSE ' ' END + SPACE(10) AS [Latin1_General_CI_AS],
CASE WHEN CHAR(nm.[DecimalValue]) LIKE '%[^a-z0-9A-Z]%' COLLATE Latin1_General_100_BIN2
THEN 'x' ELSE ' ' END + SPACE(12) AS [Latin1_General_100_BIN2],
CASE WHEN SQL#.RegEx_IsMatch(CHAR(nm.[DecimalValue]), N'\p{P}', 1, NULL) = 1
THEN 'x' ELSE ' ' END AS [RegExUnicodeCategory]
FROM nums nm;
-- ============================================================
-- = RESULTS:
-- ============================================================
/*
Latin1_ Latin1_ RegEx
Decimal Character General General Unicode
Value _CI_AS _100_BIN2 Category
0 x x
1 x x
2 x x
3 x x
4 x x
5 x x
6 x x
7 x x
8 x x
9 x x
10 x x
11 x x
12 x x
13 x x
14 x x
15 x x
16 x x
17 x x
18 x x
19 x x
20 x x
21 x x
22 x x
23 x x
24 x x
25 x x
26 x x
27 x x
28 x x
29 x x
30 x x
31 x x
32 x x
33 ! x x x
34 " x x x
35 # x x x
36 $ x x
37 % x x x
38 & x x x
39 ' x x x
40 ( x x x
41 ) x x x
42 * x x x
43 + x x
44 , x x x
45 - x x x
46 . x x x
47 / x x x
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
58 : x x x
59 ; x x x
60 < x x
61 = x x
62 > x x
63 ? x x x
64 @ x x x
65 A
66 B
67 C
68 D
69 E
70 F
71 G
72 H
73 I
74 J
75 K
76 L
77 M
78 N
79 O
80 P
81 Q
82 R
83 S
84 T
85 U
86 V
87 W
88 X
89 Y
90 Z
91 [ x x x
92 \ x x x
93 ] x x x
94 ^ x x
95 _ x x x
96 ` x x
97 a
98 b
99 c
100 d
101 e
102 f
103 g
104 h
105 i
106 j
107 k
108 l
109 m
110 n
111 o
112 p
113 q
114 r
115 s
116 t
117 u
118 v
119 w
120 x
121 y
122 z
123 { x x x
124 | x x
125 } x x x
126 ~ x x
127 x x
128 € x x
129 x x
130 ‚ x x x
131 ƒ x x
132 „ x x x
133 … x x x
134 † x x x
135 ‡ x x x
136 ˆ x x
137 ‰ x x x
138 Š x x
139 ‹ x x x
140 Πx x
141 x x
142 Ž x x
143 x x
144 x x
145 ‘ x x x
146 ’ x x x
147 “ x x x
148 ” x x x
149 • x x x
150 – x x x
151 — x x x
152 ˜ x x
153 ™ x x
154 š x x
155 › x x x
156 œ x x
157 x x
158 ž x x
159 Ÿ x x
160 x x
161 ¡ x x x
162 ¢ x x
163 £ x x
164 ¤ x x
165 ¥ x x
166 ¦ x x
167 § x x
168 ¨ x x
169 © x x
170 ª x x
171 « x x x
172 ¬ x x
173 x x x
174 ® x x
175 ¯ x x
176 ° x x
177 ± x x
178 ² x x
179 ³ x x
180 ´ x x
181 µ x x
182 ¶ x x
183 · x x x
184 ¸ x x
185 ¹ x x
186 º x x
187 » x x x
188 ¼ x x
189 ½ x x
190 ¾ x x
191 ¿ x x x
192 À x
193 Á x
194 Â x
195 Ã x
196 Ä x
197 Å x
198 Æ x
199 Ç x
200 È x
201 É x
202 Ê x
203 Ë x
204 Ì x
205 Í x
206 Î x
207 Ï x
208 Ð x x
209 Ñ x
210 Ò x
211 Ó x
212 Ô x
213 Õ x
214 Ö x
215 × x x
216 Ø x
217 Ù x
218 Ú x
219 Û x
220 Ü x
221 Ý x
222 Þ x x
223 ß x
224 à x
225 á x
226 â x
227 ã x
228 ä x
229 å x
230 æ x
231 ç x
232 è x
233 é x
234 ê x
235 ë x
236 ì x
237 í x
238 î x
239 ï x
240 ð x x
241 ñ x
242 ò x
243 ó x
244 ô x
245 õ x
246 ö x
247 ÷ x x
248 ø x
249 ù x
250 ú x
251 û x
252 ü x
253 ý x
254 þ x x
255 ÿ x
*/
Comments