Search code examples
sqldelphims-access-2016

Problem using hat in square brackets [^] in TFDQuery "like" pattern

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.

Solution

  • 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'