How to match text ending with a text on DB2?

Question

I've executed the query on DB2:

SELECT * FROM MYTABLE WHERE MYFIELD LIKE '%B' 

Although I know there are records that end with 'B' in the database, the query returned no results. After some research, it seems that DB2 is not recognizing LIKE expressions that don't end with '%'. So the following query would work:

SELECT * FROM MYTABLE WHERE MYFIELD LIKE '%B%' 

but naturally not as expected, because it will return only the rows, where MYFIELD contains 'B', but doesn't end with it.

How to go around that strage, hmmm, feature? How to match the text on the end of the word in LIKE-like expressions?

Answer 1

DB2 can match patterns at the end of the string. The problem is probably that there are other characters.

You can try:

WHERE rtrim(MYFIELD) LIKE '%B'

You can also look at the lengths of the field and delimit the string value to see if there are other characters:

select length(MyField), '|' || MyField || '|'
from mytable
where MyField like '%B%';