selecting special characters

[Elizabeth Lester]

Hi,
I would like to select all items from my Oracle 8 database that contain three underscores in a row. I know that the underscore is a special character, and when I try a regular select like this:

select * from usage_log ul, users u
where ul.user_id = u.user_id
and u.client_id = 69
and ul.oid like '%___%';

It returns all entries that contain 3 underscores, regardless of whether they are in a row or not.
My experience with the chr() command is sketchy, but I thought this would be the proper syntax (to select items with at least one underscore):

select * from usage_log ul, users u
where ul.user_id = u.user_id
and u.client_id = 69
and ul.oid like '%' || chr(154) || '%';

but that returns nothing, as does a similar search using chr(138)--i'm not sure which one is the appropriate underscore to use (if either one).
Is there another approach I should be using to select special characters?
Thanks for any ideas,
Liz

[Elizabeth Lester]

Hi,
Just wanted to post the solution:
select * from usage_log ul, users u
where ul.user_id = u.user_id
and u.client_id = 69
and ul.oid like '%\_\_\_%' ESCAPE '\';
I did not know that the underscore, when used with a 'like', represents a space, so it needs to be escaped out like this.
--liz

[Mark Spritzler]

Cool. Thanks for the information.
Mark