How to Split a Delimited VARCHAR Column in SQL for Substring Matching?

How to Split a Delimited VARCHAR Column into Multiple Values in SQL and Perform Substring Matches

Your original query attempts to filter the "AD_Ref_List" table based on a delimited list of values stored in the "xx_insert" table. However, Oracle treats the comma-separated values as one string, resulting in no matching rows.

To resolve this issue, consider the following approaches:

Using a String Substring Match with Delimiters:

Wrap both the column value and the delimited list in delimiters and perform a substring match. This ensures that exact matches are found within the delimited list.

SELECT r.Value
FROM   AD_Ref_List r
       INNER JOIN xx_insert x
       ON ( ',' || x.XX_DocAction_Next || ',' LIKE '%,' || r.value || ',%' )
WHERE  r.AD_Reference_ID = 1000448
AND    x.xx_insert_id    = 1000283;

Using a Regular Expression to Split the Delimited List:

Extract individual values from the delimited list using a regular expression and subquery. Then, perform a value-based match in the main query.

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, '[^,]+' )
);

Considerations:

1. Choose the approach that best fits your performance and data requirements.
2. Ensure that both the column value and the delimited list are consistently formatted with the same delimiter.
3. Consider using a stored procedure or function to encapsulate the splitting logic for reusability and easier maintenance.

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