Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Check multiple positions of Oracle string

 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from table x where instr('1', substr(4,1)||substr(7,1)||substr(...blabla...)) != 0
 
Aurelian Tutuianu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic