[SOLVED] Return rows which contain a string in a collection

Return rows which contain a string in a collection

I am looking to return only the rows which contain a certain string in their VARRAY.

Table Definition:

create table studentClasses(
student_id INTEGER,
full_name VARCHAR2(30),
phone_nos varray_num,
classes varray_class,
subjects_registered nestedsubjecttable, 
home_address O_ADDRESS ,
next_of_kin VARCHAR2(30),
nok_address O_ADDRESS 
)nested table subjects_registered store as ext_classes;

Part of my inserts

insert into studentClasses values(1, 'John Doe',
                              varray_num('1', '2', '3', '4', '5', '6'),
                              ....

I want to be able to return all entries which contain a specific number in their phone_nos VARRAY. So return people with '6' in their varray.

Any ideas?

Solution

Try

Select student_id ,full_name
From studentClasses t1,
Table(t1.phone_nos) t2
Where Column_Value = 6;

Hope this helps.

Reference: Day 57: PL/SQL - 5 (VArray in Pl/SQL)

N.B.~ I don't have an environment to check, but it should work