How to Split Comma-Separated VARCHAR Column Values in SQL?

Splitting VARCHAR Column Values in SQL

In SQL, the VARCHAR data type stores variable-length character strings. When dealing with VARCHAR columns, it may be necessary to split the column values into multiple individual values.

Suppose you have a SQL SELECT statement like this:

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

This statement returns all the values from the Value column in the AD_Ref_List table where the AD_Reference_ID column is equal to 1000448. The result may look like this:

Value

CO,VO

In this result, the Value column contains a comma-separated list of values. To filter the results further, you may want to split the values into individual rows, such as:

Value

CO

VO

One way to achieve this is to use the IN operator, like this:

SELECT 
  AD_Ref_List.Value
FROM AD_Ref_List
WHERE AD_Ref_List.AD_Reference_ID= 1000448
AND AD_Ref_List.Value IN
  (SELECT xx_insert.XX_DocAction_Next
  FROM xx_insert
  WHERE xx_insert_id = 1000283
  )
;

However, this query may return no rows because Oracle treats the list of values as a single string, not as individual values. To fix this, you can use the following technique:

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 query uses the LIKE operator to check if the value from the AD_Ref_List table is a substring of the list of values from the xx_insert table. This will return the desired result, with each value in the Value column appearing as a separate row.

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