How to build a COBOL var to use with DB2 LIKE with an ESCAPE character

I have DB2 cursor to pull in a list of matching names from a DB2 name table.
My issue is attempting to build a proper COBOL hostvariaable for the LIKE when the search name contains a DB2 wildcard (%_).
For example: FIVE % STORES
When the name does contain one I'll need to add an ESCAPE character so the wildcard is not seen as a wildcard.
The full LIKE syntax would be: LIKE '%FIRST \% STORES' ESCAPE '\'
If I put the above in a SPUFI it works fine but not if I populate a COBOL variable with '%FIRST \% STORES' ESCAPE '\'.

If I STRING single quotes around %FIVE \% STORES so the COBOL hostvar contains:
The search attempts to locate names that start and end with single quotes

If I do NOT STRING single quotes around %FIVE \% STORES so the COBOL hostvar contains:
The search does not work as I believe attempts to locate names with the work ESCAPE in it.

Same with the single quotes around the ESCAPE character, if I do NOT have the quotes
The search does not work.

Any suggestions or insights would be greatly appreciated!


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


the ESCAPE is a part of the LIKE predicate so you can't put it in the cobol variable.
i assume that you have logic which adds the \ to the content of the variable, so in that case, your code should look like this

exec sql
 select ...
  where col_name like :variable ESCAPE '\'

and your variable would only contain the actual pattern

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billgeoAuthor Commented:
I'll go give that a shot and let you know.
billgeoAuthor Commented:
So far I've been unable to get it to work and pull in the test name I have setup to be pulled in by the above string.  I'm assuming I have some other issue so I'll keep digging to find it.

Do you know if I can make the actual ESCAPE character a hostvariable?
exec sql
 select ...
  where col_name like :variable ESCAPE :WS-ESC-CHR

That way I can change it incase the search string contains the default escape character in the string.
i.e., If I coded a static '\' as the ESCAPE character as you have above but the search string has a slash in the string, wouldn't I have to change the ESCAPE character to something other than the slash?
I would think I'd lose the slash in as part of the name if it were not changed.

billgeoAuthor Commented:
billgeoAuthor Commented:
GOT IT!!  Thanks, your solution was perfect.

And Yes, you can code a hostvariable for the actual ESCAPE character.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.