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

Question

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.

Access SQL `LIKE` doesn't do Regex. https://stackoverflow.com/questions/5539141/microsoft-office-access-like-vs-regex — Andre, Feb 01 '20 at 12:26

Lee Mac · Accepted Answer · 2020-02-02T15:56:38.310

6

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'

edited Feb 02 '20 at 15:56

answered Feb 01 '20 at 12:54

Lee Mac

15,615
6
32
80

1

Don't forget to escape the `!` char when FireDAC macros are enabled. – TLama Feb 01 '20 at 14:33
Unfortunately the proposed script returns only `acc` when used in TFDQuery – Rati2019 Feb 01 '20 at 14:45
Could you please prompt me how can I escape it? – Rati2019 Feb 01 '20 at 16:48
2

@Rati, see [Special Character Processing](http://docwiki.embarcadero.com/RADStudio/Rio/en/Preprocessing_Command_Text_(FireDAC)#Special_Character_Processing) topic. – TLama Feb 02 '20 at 04:31

score 1 · Answer 2 · answered Feb 02 '20 at 15:53

1

The correct code is:

Select * from Table1 where Field1 like 'a[!!a-c]c'

answered Feb 02 '20 at 15:53

Rati2019

91
9

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

2 Answers2