'LIKE' does'nt like UNDERSCORE

Write a query to return those values from an Oracle table where the column myword contains an UNDERSCORE ( _ ) sign in its values. e.g if the column 'myword' contains following values:

myword

----------

FAC_FAC

FACFAC

_FACE

FACE

FACE_

The output should be:

myword

----------

FAC_FAC

_FACE

FACE_

Solution:

Generally, most people will write the query as follows:

select myword from mytable where myword like '%_%'.

However this query will not give expected results. It will return all the values of column myword.

The correct query will be as follows:

select myword from mytable where myword like '%\_%' escape '\'

We will have to escape the '_' character to exactly match it.