CODEWITHSUNDEEP
oracle-database
John Doe
John Doe

Reputation: 3233

How to effectively search on a delimited string in Oracle?

I have an Oracle 11g table setup as:

CODE   VERSION   STRING_DATA 
AAA    1         aaa|bbb|EARTH|ddd|eee
AAA    1         aaa|bbb|MARS|ddd|eee
AAA    2         aaa|b22|EARTH|d22|eee
BBB    1         aaa|b22|EARTH|d22|eee

Now the STRING_DATA is broken down as:

  1. col1
  2. col2
  3. location
  4. col4
  5. col5

How could I write a query similar to my psuedo-code?

SELECT *
FROM MY_TABLE
WHERE CODE = 'AAA' AND VERION = 1 AND LOCATION = 'EARTH

Upvotes: 0

Views: 26

Answers (2)

user5683823
user5683823

Reputation: 

with
     my_table ( code, version, string_data ) as (
       select 'AAA', 1, 'aaa|bbb|EARTH|ddd|eee' from dual union all
       select 'AAA', 1, 'aaa|bbb|MARS|ddd|eee'  from dual union all
       select 'AAA', 2, 'aaa|b22|EARTH|d22|eee' from dual union all
       select 'BBB', 1, 'aaa|b22|EARTH|d22|eee' from dual
     )
select *
from my_table
where code = 'AAA' and version = 1 and regexp_like(string_data, '^[^|]*\|[^|]*\|EARTH\|')
;
COD    VERSION STRING_DATA
--- ---------- ---------------------
AAA          1 aaa|bbb|EARTH|ddd|eee

Upvotes: 0

vercelli
vercelli

Reputation: 4757

Use Substr and Instr

   select * 
     from MY_Table
    where CODE = 'AAA' 
       AND VERION = 1 
       AND substr(string_data,instr(string_data,'|',1,2)+1, instr(string_data,'|',1,3)-instr(string_data,'|',1,2)-1)  = 'EARTH';

Upvotes: 2

Related Questions