How can i select an element from a Varray with the like Keyword

Question

can somebody please help me with how to select a phone array in sql statement where the phone is an array

Create type phone as varray (3) of varchar2(13);

select name, phone from person_table where value(p) is of (type) and ( phone starts with '0770');

As an aside: "starts with" combined with "%..." doesn't make sense. — Koen, Mar 17 '15 at 14:27
yeah i know that but i just used that to explain it well i need the phone array to select the one that starts with 0770 — Timmy Barr, Mar 17 '15 at 15:03

score 1 · Answer 1 · answered Mar 17 '15 at 15:04

Take a look at this sample:

Create type phone_v as varray (3) of varchar2(13); --type creation
Create table person (name varchar2(100),phone phone_v); --table creation

--table data
insert into person values ('John',phone_v('0770 12','0789 00','0101'));
insert into person values ('David',phone_v('1','1','1'));

Now you can do something like this:

select * from person per
where exists (select 1 from table(per.phone) where column_value like '%0770%');

This query get data of all persons who have a phone number that contains 0770, if you wants phones that starts with this number just change the like expression by 0770%

How can i select an element from a Varray with the like Keyword

1 Answers1