如何在 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, '[^,]+' )
);
相关文章