I am using DB2v9.1.0.356 . I am trying to retrieve the DB2 users whose name ends with "R" , using the following query.

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where GRANTEE like '%R' or GRANTEE = '%R'

This query returns only those names which has more than eight characters; like ADMINISTRATOR , DAVIDROGER etc , but doesn't returns user names having less than eight characters like USER , ROGER.

But if query the DB to retrieve users starting with R using the following query it returns all matching results , irrespective of number of characters.

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where GRANTEE like 'R%' or GRANTEE = 'R%'
It returns users like ROGER , REYNOLD etc.

Please advise!

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where RTRIM(GRANTEE) like '%R';

1) GRANTEE might be stored with trailing blanks.
2) If GRANTEE was '%R', it will match with pattern '%R'. Because '%' matches any character(s) including '%' or no character.

It works! Thanks a lot. So does DB2 add trailing blanks for entries shorter than eight characters ?

I hope it is OK to tack a question onto your answer here.

How can modify the LIKE '%R' predicate so that R is actually a host (client side) variable? I'm not having any luck concatenating the parameter marker with the wild card.

Thanks.

No, it is not a good idea ot add on to this thread. Please open a new thread and include your OS. DB2 version level, type of programming language, and the exact statement you are trying to submit to DB2 (if you have already tried one), and any error messages you have received.

Gotcha, and thanks for the feedback. I resolved my problem, but I'll remember your advice next time.