How to Efficiently Split and Filter a Comma-Separated VARCHAR Column in SQL?

Splitting a VARCHAR Column into Multiple Values in SQL

The VARCHAR data type in SQL allows for the storage of variable-length character data. Often, it is necessary to split a VARCHAR column into multiple values to facilitate data filtering or other operations.

Consider the following example:

SELECT 
  AD_Ref_List.Value
FROM AD_Ref_List
WHERE AD_Ref_List.AD_Reference_ID= 1000448

This query retrieves a column named "Value" from the table "AD_Ref_List" where the column "AD_Reference_ID" is equal to 1000448. The result is a list of values:

CO,VO

However, the requirement is to limit the results based on values stored in a different table, "xx_insert":

SELECT xx_insert.XX_DocAction_Next
  FROM xx_insert
  WHERE xx_insert_id = 1000283

The result of this query is:

CO

The challenge lies in modifying the original query to incorporate the values from "xx_insert" while maintaining the comma-separated format. The initial attempt transformed the values into a delimited string:

AD_Ref_List.Value IN ('CO,VO')

Which is not the desired result.

Solution:

To address this issue, the values in the IN clause should be wrapped with the appropriate delimiter and checked against a comma-separated list also enclosed with delimiters:

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;

This technique allows for the identification of values that match the comma-separated list. Alternatively, a more efficient approach using row-value filtering can also be employed:

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 <p>Both solutions effectively split the VARCHAR column into multiple values and filter based on the specified values, resolving the issue encountered in the initial query.</p>

The above is the detailed content of How to Efficiently Split and Filter a Comma-Separated VARCHAR Column in SQL?. For more information, please follow other related articles on the PHP Chinese website!