如何在 SQL 中将 varchar 列拆分为多个值?

我有这个 SQL Select 语句

SELECTAD_Ref_List.ValueFROM AD_Ref_ListWHERE AD_Ref_List.AD_Reference_ID= 1000448

这是SELECT的结果:

为了限制选定的行，我在其他表中存储了几个值，如下所示:

SELECT xx_insert.XX_DocAction_Next从 xx_insert哪里 xx_insert_id = 1000283

所以，我最后的 SQL Select 是这样的:

SELECTAD_Ref_List.ValueFROM AD_Ref_ListWHERE AD_Ref_List.AD_Reference_ID= 1000448AND AD_Ref_List.Value IN(选择 xx_insert.XX_DocAction_Next从 xx_insert哪里 xx_insert_id = 1000283);

问题:这个 SELECT 没有返回任何行，因为 Oracle 已经转换成这样:AD_Ref_List.Value IN ('CO,VO')

但是，我需要的是:AD_Ref_List.Value IN ('CO','VO')

我该怎么做???

最好的问候

解决方案

将你在分隔列表中使用的分隔符中的值包裹起来，然后检查它是否是分隔列表的子字符串(也包括分隔符包裹围绕它):

SELECT r.ValueFROM AD_Ref_List r内连接 xx_insert xON ( ',' || x.XX_DocAction_Next || ',' LIKE '%,' || r.value || ',%' )WHERE r.AD_Reference_ID = 1000448AND x.xx_insert_id = 1000283;

<块引用>

我必须在 whereClause 中保留逻辑

真的，不要.上面的查询会更有效率.

但如果你必须这样做:

SELECT 值FROM AD_Ref_ListWHERE AD_Reference_ID = 1000448与值输入 (SELECT REGEXP_SUBSTR( XX_DocAction_Next, '[^,]+', 1, LEVEL )从 xx_insert哪里 xx_insert_id = 1000283按级别连接 <= REGEXP_COUNT( XX_DocAction_Next, '[^,]+' ));

I have this SQL Select Statement

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

This is the result of SELECT:

To limit the selected rows, I have a couple of values stored in other table like this:

SELECT xx_insert.XX_DocAction_Next
  FROM xx_insert
  WHERE xx_insert_id = 1000283

So, My final SQL Select is 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
  )
;

PROBLEM : This SELECT return no line, because Oracle has transformed like this: AD_Ref_List.Value IN ('CO,VO')

But, what I need is : AD_Ref_List.Value IN ('CO','VO')

How can I do this???

Best regards

解决方案

Wrap the value in the delimiter you are using in the delimited list and then check if it is a sub-string of the delimited list (also with the delimiters wrapped around it):

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;

i must keep the logic in the whereClause

Really, don't. The above query will be much more efficient.

But if you have to then:

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