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:
 '%FIRST \% STORES' ESCAPE '\'
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:
%FIRST \% STORES ESCAPE '\'
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
%FIRST \% STORES ESCAPE \
The search does not work.

Any suggestions or insights would be greatly appreciated!

Thanks
Bill

billgeoAsked:
Who is Participating?
 
momi_sabagCommented:
bill

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
0
 
billgeoAuthor Commented:
I'll go give that a shot and let you know.
Thanks!
0
 
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?
e.g.  
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.

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

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

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.