• 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic