SQL:1999 Array Type Constructor Usage?

Question

Can anyone confirm whether or not the SQL:1999 Array type Constructor provides any operations for searching the Array in a WHERE clause?.

As an Example If a table EMPLOYEES had a column

QUALIFICATION VARCHAR(20) ARRAY[10]

containing values such as ARRAY['BSC','MBA']

Does the standard support some way of querying EMPLOYEES to find all Employees with an MBA?

Answer 1

Well, you can always use an element reference (ISO/IEC 9075-2:1999, 6.13 ):

WHERE QUALIFICATION(1) = 'BSC' 
   OR QUALIFICATION(2) = 'BSC'
   ... 

Of course, the problem is that you need to write a comparison for each possible position.

I am not aware of any operators that allows you to compare a scalar with an array, although I would suppose a DBMS that has native support for ARRAY types ould let you create a function that does the job.

I must say I never had the need for array types - I would typically build a one-to-many detail table, or in rare cases, add multiple columns (yeah - a repeating group. send the relational police to hunt me if you like :)

Would you care to explain why you need to know this, or what problem you are trying to solve with an ARRAY?