I have MS Access 2016 table and running TFDQuery in Delphi 10.3.
Field1 values are: aac
, abc
, acc
, a c
, azc
, ac
, azzc
I run query:
Select * from Table1 WHERE Field1 like 'a[^a-c]c'
Referring to regex match any single character (one character only), I should get:
"a c, azc"
but I am getting
"aac, abc, acc"
Please help to correct the script.
To match a negative character set using the like
operator in MS Access, you should use the exclamation mark, per the documentation, e.g.:
select * from Table1 where Field1 like 'a[!a-c]c'
Your current code is matching the characters ^
,a
,b
,c
surrounded by the characters a
& c
, hence explaining the results you are currently receiving.
Per the comments below by @TLama, you'll also need to escape the exclamation mark, e.g.:
select * from Table1 where Field1 like 'a[!!a-c]c'