Does Oracle field contain all letters from a string?

Question

There is a VARCHAR2 field in an Oracle table. It can contain combinations of letters in any order:

• BOWLD
• DLBWO
• OWB
• BW

etc.

Then I have another string as SQL query parameter. It also contains similar letters.

I would like to detect whether the field contains ALL letters from the parameter or not.

Example:

Field   Parameter Result
BLWOD     WB       True
OBLD      BD       True
OBLD      BDW      False

If it were just one letter in the parameter, I could simply use the LIKE clause:

SELECT MyObjID FROM MyTable WHERE ColorField LIKE :ColorParam

But what to do when I have to split the :ColorParam into letters and do multiple evaluations at a single time?

Answer 1

You can use a correlated sub-query to split the parameter into letters and check that all the letters are in field:

SELECT field,
       parameter,
       CASE
       WHEN 0 < ALL(
                  SELECT INSTR(field, SUBSTR(parameter, LEVEL, 1))
                  FROM   DUAL
                  CONNECT BY LEVEL <= LENGTH(parameter)
                )
       THEN 'true'
       ELSE 'false'
       END AS result
FROM   table_name 

Which, for the sample data:

CREATE TABLE table_name (Field, Parameter) AS
SELECT 'BLWOD', 'WB'  FROM DUAL UNION ALL
SELECT 'OBLD',  'BD'  FROM DUAL UNION ALL
SELECT 'OBLD',  'BDW' FROM DUAL;

Outputs:

FIELD	PARAMETER	RESULT
BLWOD	WB	true
OBLD	BD	true
OBLD	BDW	false

fiddle