How to search text for an underscore '_' character?

[kjnorman]

Okay I feel pretty stupid right at this moment, as I feel that this should be an easy answer but here I go.

I want to search a text field and return rows where there is an underscore '_' in the text. The problem is, is that the underscore is a single character wildcard as you all well know.

Currently I translate the searched text this way:

translate(my_searched_field,'_','~') like '%~%'

and it works, but is there a better way?

Thanks.

 

[SantaMufasa]

I believe so:

...WHERE my_searched_field like '%#_%' escape '#';

You can use any character you like for the escapte character. (But you probably don't want to use the "_" and "%" characters .)

Let us know how you like this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

or perhaps:

WHERE instr(my_searched_field,'_') > 0

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus