How to use variable in a sql statement with the like operator

What is the correct way to write embedded SQL that will use a Like operator and local variable. Ex How can a sql query return the IDs of all rows where LastName has the substring "Doe"?

Set nameToFind="Doe". The below does not work.

&sql(Select ID from myTable where LastName like '%:nameToFind%')

Simple enough but this syntax is tricky!!

  • 0
  • 0
  • 669
  • 0
  • 3

Answers

Try

Set nameToFind="%Doe%". The below does not work.

&sql(Select ID from myTable where LastName like :nameToFind)

 

It works !! Thanks Anil . So single quotes are not needed !!

Within an SQL statement, the variable starts with a (:) colon.  

If you put the quote it becomes a fixed value.

                                        ':nameToFind'   is interperted as    ":nameToFind"

Set nameToFind="abc"

                                        :nameToFind is interpreted as    "abc"

Regards

Anil