File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes selecting special characters Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "selecting special characters" Watch "selecting special characters" New topic
Author

selecting special characters

Elizabeth Lester
Ranch Hand

Joined: Aug 14, 2001
Posts: 129
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
Ranch Hand

Joined: Aug 14, 2001
Posts: 129
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Cool. Thanks for the information.
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: selecting special characters
 
Similar Threads
---= sql String =---
Search Strategy
SQL Help Please
Problem in executing Oracle Function from JSP Page
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"