How do you search for a string in a varray in SQL (Oracle)

Question

How would I query a varray attribute for a specific string, such as a phone number array that contains the number 013579 or begins with 0883?

Also, how to I query to count the number of values in an array (e.g. check if a varray contains 3 values)

Answer 1

If it was a collection then you could use the MEMBER OF operator to find if an element was contained in a collection or the CARDINALITY function to check the size of the collection; however, those do not work on a VARRAY and, instead, you would need to use a table collection expression.

If you have the table:

CREATE TYPE phone_no_list AS VARRAY(10) OF VARCHAR2(15);

CREATE TABLE people (
  name VARCHAR2(20),
  phone_nos phone_no_list
);

INSERT INTO people ( name, phone_nos )
SELECT 'Alice', phone_no_list( '013579', '013560' ) FROM DUAL UNION ALL
SELECT 'Beryl', phone_no_list( '088300', '088299' ) FROM DUAL UNION ALL
SELECT 'Carol', phone_no_list( '123456', '987654' ) FROM DUAL UNION ALL
SELECT 'Doris', phone_no_list( '000000', '111111', '222222' ) FROM DUAL;

Then you can use:

SELECT name
FROM   people p
WHERE  EXISTS(
         SELECT 1
         FROM   TABLE( p.phone_nos )
         WHERE  COLUMN_VALUE = '013579'
         OR     COLUMN_VALUE LIKE '0883%'
       )
OR     ( SELECT COUNT(*) FROM TABLE( p.phone_nos ) ) = 3;

Which outputs:

| NAME  |
| :---- |
| Alice |
| Beryl |
| Doris |

Or:

SELECT p.name,
       n.COLUMN_VALUE AS phone_no
FROM   people p
       CROSS APPLY TABLE( p.phone_nos ) n
WHERE  n.COLUMN_VALUE = '013579'
OR     n.COLUMN_VALUE LIKE '0883%'

Which outputs:

NAME  | PHONE_NO
:---- | :-------
Alice | 013579  
Beryl | 088300  

db<>fiddle here