Home >Database >Mysql Tutorial >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!