*
The moose likes Oracle/OAS and the fly likes Check multiple positions of Oracle string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Check multiple positions of Oracle string" Watch "Check multiple positions of Oracle string" New topic
Author

Check multiple positions of Oracle string

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Hi All,

I need to write an Oracle query and select all records if one field has "character 1" in 11 different positions.

I have a 25 character string whose 25 characters denote specific positions.

i have to see whether 1 exists in either 4th character or 7th character or 8th character or 9th or 11th or 17th or 21st. These positions are constant.

Right now I have achived it using

select * from table x where (check_string like '___1%' or check_string like '______1%' or check_string like '_______1%' ) // like this for all characters.

Whats the better way that I can handle this ?

Thanks
Atul
Aurelian Tutuianu
Ranch Hand

Joined: May 13, 2004
Posts: 86
select * from table x where instr('1', substr(4,1)||substr(7,1)||substr(...blabla...)) != 0


http://javasign.blogspot.com/
Aurelian Tutuianu
Ranch Hand

Joined: May 13, 2004
Posts: 86
My strong suggestion is to create separate columns for these information and select using columns operations. Avoid string processing because it kills performance.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Check multiple positions of Oracle string
 
Similar Threads
Escape HQL Like special characters
Parsing using Java
using wildcards literally in queries
Random String (Hash)
Does varchar allow non-printable characters