How to Split a Delimited VARCHAR Column for Efficient SQL Queries?

Splitting a VARCHAR Column into Multiple Values in SQL

Problem:

When retrieving values from a VARCHAR column using an IN clause, Oracle transforms the delimited list into a single-quoted string, preventing the sub-query from matching individual values.

Solution 1:

Wrap the delimited list values in the IN clause with the same delimiter used in the VARCHAR column. For example, instead of using AD_Ref_List.Value IN ('CO','VO'), use AD_Ref_List.Value IN (',' || 'CO' || ',' || 'VO' || ',').

Solution 2 (Alternative Where Clause):

Use a regular expression-based technique with the REGEXP_SUBSTR function to extract individual values from the delimited list at specified levels. For example, the following query uses recursion to split the delimited list:

SELECT Value
FROM AD_Ref_List
WHERE AD_Reference_ID = 1000448
AND value IN (
  SELECT REGEXP_SUBSTR(XX_DocAction_Next, '[^,]+', 1, LEVEL)
  FROM xx_insert
  WHERE xx_insert_id = 1000283
  CONNECT BY LEVEL <= REGEXP_COUNT(XX_DocAction_Next, '[^,]+')
);

The above is the detailed content of How to Split a Delimited VARCHAR Column for Efficient SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!