"Why does the statement
Field1 like "*[!a-z0-9]*" in the where clause work properly for all ASCII characters in the first 127 ASCII characters but for ASCII characters 128 to 255 it selects some but not all, when it in fact should be selecting all of them.
I'm far, far from an expert at regular expressions, but I can throw out a guess. Maybe it's the dual possible use for *. This has an effect on a RE, no? In Access, it's also a wild card. So is it being interpreted as (Access parts in red)
LIKE any part of table field & specified characters from an expression & any remaining part of table field ,
or is it
LIKE specified characters from an expression that happens to have * as one of the 'operators'
To further illustrate my meaning, I tried concatenating the * so it was utilized only by Access, to the expression result as in
Code:
SELECT tblAscii.CodeValue, tblAscii.Character, Switch([tblAscii].[Character] Like "*" & "[!a-z0-9]" & "*",[tblAscii].[Character]) AS Expr1
FROM tblAscii WHERE Character Like "*" & "[!a-z0-9]" & "*";
What I got is 88 characters that are definitely not alphanumeric, but I think it's still not what you want. From what little I've been able to absorb from past studying of RE web pages is that not only are some expression operators limited to one platform or another, what is considered to be an alpha character is governed by the system code page (or some similar obscure thing). A search I did on operators didn't even list the ! you are using as one of them, so who really knows, but I tried it without ! and got 188 records of what I would call alpha. So you may not want Æ in your results (I did get that one when not using !) but while it may be Cyrillic or whatever, I'll bet it's considered alpha. Without ! I got the following list (note, I removed rows where the description of the character was all alpha but the character itself is not, such as NULL)
CodeValue |
Character |
Expr1 |
49 |
0 |
0 |
50 |
1 |
1 |
51 |
2 |
2 |
52 |
3 |
3 |
53 |
4 |
4 |
54 |
5 |
5 |
55 |
6 |
6 |
56 |
7 |
7 |
57 |
8 |
8 |
58 |
9 |
9 |
66 |
A |
A |
67 |
B |
B |
68 |
C |
C |
69 |
D |
D |
70 |
E |
E |
71 |
F |
F |
72 |
G |
G |
73 |
H |
H |
74 |
I |
I |
75 |
J |
J |
76 |
K |
K |
77 |
L |
L |
78 |
M |
M |
79 |
N |
N |
80 |
O |
O |
81 |
P |
P |
82 |
Q |
Q |
83 |
R |
R |
84 |
S |
S |
85 |
T |
T |
86 |
U |
U |
87 |
V |
V |
88 |
W |
W |
89 |
X |
X |
90 |
Y |
Y |
91 |
Z |
Z |
98 |
a |
a |
99 |
b |
b |
100 |
c |
c |
101 |
d |
d |
102 |
e |
e |
103 |
f |
f |
104 |
g |
g |
105 |
h |
h |
106 |
i |
i |
107 |
j |
j |
108 |
k |
k |
109 |
l |
l |
110 |
m |
m |
111 |
n |
n |
112 |
o |
o |
113 |
p |
p |
114 |
q |
q |
115 |
r |
r |
116 |
s |
s |
117 |
t |
t |
118 |
u |
u |
119 |
v |
v |
120 |
w |
w |
121 |
x |
x |
122 |
y |
y |
123 |
z |
z |
129 |
Ç |
Ç |
130 |
ü |
ü |
131 |
é |
é |
132 |
â |
â |
133 |
ä |
ä |
134 |
à |
à |
135 |
å |
å |
136 |
ç |
ç |
137 |
ê |
ê |
138 |
ë |
ë |
139 |
è |
è |
140 |
ï |
ï |
141 |
î |
î |
142 |
ì |
ì |
143 |
Ä |
Ä |
144 |
Å |
Å |
145 |
É |
É |
146 |
æ |
æ |
147 |
Æ |
Æ |
148 |
ô |
ô |
149 |
ö |
ö |
150 |
ò |
ò |
151 |
û |
û |
152 |
ù |
ù |
153 |
ÿ |
ÿ |
154 |
Ö |
Ö |
155 |
Ü |
Ü |
156 |
ø |
ø |
158 |
Ø |
Ø |
160 |
ƒ |
ƒ |
161 |
á |
á |
162 |
í |
í |
163 |
ó |
ó |
164 |
ú |
ú |
165 |
ñ |
ñ |
166 |
Ñ |
Ñ |
167 |
ª |
ª |
168 |
º |
º |
172 |
½ |
½ |
173 |
¼ |
¼ |
182 |
Á |
Á |
183 |
 |
 |
184 |
À |
À |
199 |
ã |
ã |
200 |
à |
à |
209 |
ð |
ð |
210 |
Ð |
Ð |
211 |
Ê |
Ê |
212 |
Ë |
Ë |
213 |
È |
È |
214 |
ı |
ı |
215 |
Í |
Í |
216 |
Î |
Î |
217 |
Ï |
Ï |
223 |
Ì |
Ì |
225 |
Ó |
Ó |
226 |
ß |
ß |
227 |
Ô |
Ô |
228 |
Ò |
Ò |
229 |
õ |
õ |
230 |
Õ |
Õ |
232 |
þ |
þ |
233 |
Þ |
Þ |
234 |
Ú |
Ú |
235 |
Û |
Û |
236 |
Ù |
Ù |
237 |
ý |
ý |
238 |
Ý |
Ý |
244 |
¾ |
¾ |
252 |
¹ |
¹ |
253 |
³ |
³ |
254 |
² |
² |
So I have to think that something like ß truly satisfies the sql (at least on my system) even though you probably don't want it in the results. As for
is a lot easier than writing and compiling a function like the following for each different set of allowable characters.
I can't imagine why, especially if the problem is one of system settings or incompatibility between operators (*) used in Access that are also used in other platforms. At least the function works.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.