在Oracle 12中搜索逗号分隔的值 [英] Search comma separated value in oracle 12

问题描述

我有一个表-Product在Oracle中，其中p_spc_cat_id存储为逗号分隔的值.

I have a Table - Product In Oracle, wherein p_spc_cat_id is stored as comma separated values.

p_id p_name p_desc p_spc_cat_id
1    AA     AAAA   26,119,27,15,18
2    BB     BBBB   0,0,27,56,57,4
3    BB     CCCC   26,0,0,15,3,8
4    CC     DDDD   26,0,27,7,14,10
5    CC     EEEE   26,119,0,48,75

现在我要搜索在'26,119,7'中具有p_spc_cat_id的p_name，并且此搜索值不固定，将需要一段时间'7,27,8'.搜索文字组合每次都会更改

Now I want to search p_name which have p_spc_cat_id in '26,119,7' And this search value are not fixed it will some time '7,27,8'. The search text combination change every time

我的查询是:

select p_id,p_name from product where p_spc_cat_id in('26,119,7');

当我执行该查询时，我找不到任何结果

when i execute this query that time i can't find any result

推荐答案

我的回答很晚，但是我希望我正确理解了这个问题.

I am little late in answering however i hope that i understood the question correctly.

在以下情况下进一步阅读:您有一个表，该表存储着

Read further if: you have a table storing records like

1. 10,20,30,40
2. 50,40,20,70
3. 80,60,30,40

还有一个搜索字符串，例如"10,60"，在这种情况下，它应该返回第1& 3.

And a search string like '10,60', in which cases it should return rows 1 & 3.

请尝试以下操作，它适用于我的小桌子& ;;数据.

Please try below, it worked for my small table & data.

create table Temp_Table_Name (some_id number(6), Ab varchar2(100))
insert into Temp_Table_Name values (1,'112,120')
insert into Temp_Table_Name values (2,'7,8,100,26')

首先让我们细分逻辑:

• 该表在其中一列[AB列]中包含逗号分隔的数据.
• 我们有一个逗号分隔的字符串，需要在该字符串列中进行单独搜索. ['26，119,7,18'-X_STRING]
• ID列是表中的主键.

1.)使表中的每条记录多次x次，其中x是搜索字符串[X_STRING]中逗号分隔值的计数.我们可以使用下面的查询来创建笛卡尔联接子查询表.

1.) Lets multiple each record in the table x times where x is the count of comma separated values in the search string [X_STRING]. We can use below query to create the cartesian join sub-query table.

Select Rownum Sequencer,'26,119,7,18' X_STRING 
from dual 
CONNECT BY ROWNUM <= (LENGTH( '26,119,7,18') - LENGTH(REPLACE( '26,119,7,18',',',''))) + 1

小注释:计算逗号分隔值的数量=

Small note: Calculating count of comma separated values =

Length of string - length of string without ',' + 1 [add one for last value]

2.)创建一个函数PARSING_STRING，使得PARSING_STRING(string，position).所以，如果我通过:

2.) Create a function PARSING_STRING such that PARSING_STRING(string,position). So If i pass:

PARSING_STRING('26,119,7,18',3) it should return 7.

CREATE OR REPLACE Function PARSING_STRING
(String_Inside IN Varchar2, Position_No IN Number) 
Return Varchar2 Is
    OurEnd   Number; Beginn Number;
Begin

    If Position_No < 1 Then 
    Return Null; 
    End If;

    OurEnd := Instr(String_Inside, ',', 1, Position_No);

    If OurEnd = 0 Then
        OurEnd := Length(String_Inside) + 1;
    End If;

    If Position_No = 1 Then
        Beginn := 1;
    Else
        Beginn := Instr(String_Inside, ',', 1, Position_No-1) + 1;
    End If;

    Return Substr(String_Inside, Beginn, OurEnd-Beginn);

End;
/

3.)主查询，具有用于增加记录的联接.

3.) Main query, with the join to multiply records.:

select t1.*,PARSING_STRING(X_STRING,Sequencer) 
from Temp_Table_Name t1,
(Select Rownum Sequencer,'26,119,7,18' X_STRING from dual 
CONNECT BY ROWNUM <= (Select (LENGTH( '26,119,7,18') - LENGTH(REPLACE( 
'26,119,7,18',',',''))) + 1 from dual))  t2

请注意，对于每个相乘的记录，我们从逗号分隔的字符串中得到1个特定的位置值.

Please note that with each multiplied record we are getting 1 particular position value from the comma separated string.

4.)最终确定where条件:

4.) Finalizing the where condition:

Where
/* For when the value is in the middle of the strint [,value,] */
AB like '%,'||PARSING_STRING(X_STRING,Sequencer)||',%'
OR
/* For when the value is in the start of the string [value,] 
parsing the first position comma separated value to match*/
PARSING_STRING(AB,1) = PARSING_STRING(X_STRING,Sequencer)
OR
/* For when the value is in the end of the string [,value] 
parsing the last position comma separated value to match*/
PARSING_STRING(AB,(LENGTH(AB) - LENGTH(REPLACE(AB,',',''))) + 1) = 
PARSING_STRING(X_STRING,Sequencer)

5.)在查询中使用distinct获得唯一的ID

5.) Using distinct in the query to get unique ID's

[最终查询:上述所有逻辑的组合:1个查询以查找所有逻辑]

select distinct Some_ID
from Temp_Table_Name t1,
(Select Rownum Sequencer,'26,119,7,18' X_STRING from dual 
CONNECT BY ROWNUM <= (Select (LENGTH( '26,119,7,18') - LENGTH(REPLACE( '26,119,7,18',',',''))) + 1 from dual))  t2
Where
AB like '%,'||PARSING_STRING(X_STRING,Sequencer)||',%'
OR
PARSING_STRING(AB,1) = PARSING_STRING(X_STRING,Sequencer)
OR
PARSING_STRING(AB,(LENGTH(AB) - LENGTH(REPLACE(AB,',',''))) + 1) = PARSING_STRING(X_STRING,Sequencer)

这篇关于在Oracle 12中搜索逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！