Home >Database >Mysql Tutorial >How to Split Comma-Separated VARCHAR Column Values in SQL?

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

DDD
DDDOriginal
2024-12-16 14:37:17235browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn