AnsweredAssumed Answered

underscore in LIKE not escapable

Question asked by makerbert on Jan 18, 2017
Latest reply on Jan 18, 2017 by makerbert

Hi all,

 

I have a list of names derived from a legacy system. Some names are like "YADDAYADDA_FK", others might have names like "THISAFK" or "THATZFK" and hold data which I'm not interested in.

 

The last THREE characters are important, including the underscore. Unfortunately the underscore is a single character wildcard in sql thus the executeSQL below returns both "YADDAYADDA_FK", "THISAFK" and "THATZFK" but I really only want the first.

 

Query in use is:

ExecuteSQL (

"

SELECT count(column_name) FROM tabe_COLU

  WHERE table_name LIKE ?

       AND column_name LIKE ?

"

; "" ; "";

table_name ; "%_FK"

)

 

This query works but also returns THISAFK and THATZFK, which is expected and understood but not what I need.

 

Various internet sources state I should escape the underscore by encasing it in square brackets:

 

 

table_name ; "%[_]FK"

 

 

but if i do this nothing is returned

 

 

Anyone any idea how to escape the underscore?

Outcomes