aspose file tools*
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


Win a copy of Spring in Action this week in the Spring forum!
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Check multiple positions of Oracle string