oracle 是否可以查询特定值的逗号分隔列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7212282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to query a comma separated column for a specific value?
提问by Andy
I have (and don't own, so I can't change) a table with a layout similar to this.
我有（并且不拥有，所以我无法更改）一个布局与此类似的表格。
ID | CATEGORIES
---------------
1 | c1
2 | c2,c3
3 | c3,c2
4 | c3
5 | c4,c8,c5,c100
I need to return the rows that contain a specific category id. I starting by writing the queries with LIKE statements, because the values can be anywhere in the string
我需要返回包含特定类别 ID 的行。我首先使用 LIKE 语句编写查询，因为值可以在字符串中的任何位置
SELECT id FROM table WHERE categories LIKE '%c2%';Would return rows 2 and 3
SELECT id FROM table WHERE categories LIKE '%c2%';将返回第 2 行和第 3 行
SELECT id FROM table WHERE categories LIKE '%c3%' and categories LIKE '%c2%';Would again get me rows 2 and 3, but not row 4
SELECT id FROM table WHERE categories LIKE '%c3%' and categories LIKE '%c2%';会再次让我得到第 2 行和第 3 行，但不会得到第 4 行
SELECT id FROM table WHERE categories LIKE '%c3%' or categories LIKE '%c2%';Would again get me rows 2, 3, and 4
SELECT id FROM table WHERE categories LIKE '%c3%' or categories LIKE '%c2%';将再次让我获得第 2、3 和 4 行
I don't like all the LIKEstatements. I've found FIND_IN_SET()in the Oracle documentation but it doesn't seem to work in 10g. I get the following error:
我不喜欢所有的LIKE陈述。我FIND_IN_SET()在 Oracle 文档中找到了，但它似乎在 10g 中不起作用。我收到以下错误：
ORA-00904: "FIND_IN_SET": invalid identifier
00904. 00000 - "%s: invalid identifier"
when running this query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories);(example from the docs) or this query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories) <> 0;(example from Google)
运行此查询时：（SELECT id FROM table WHERE FIND_IN_SET('c2', categories);来自文档的示例）或此查询：（SELECT id FROM table WHERE FIND_IN_SET('c2', categories) <> 0;来自 Google 的示例）
I would expect it to return rows 2 and 3.
我希望它返回第 2 行和第 3 行。
Is there a better way to write these queries instead of using a ton of LIKEstatements?
有没有更好的方法来编写这些查询而不是使用大量LIKE语句？
回答by GolezTrol
You can, using LIKE. You don't want to match for partial values, so you'll have to include the commas in your search. That also means that you'll have to provide an extra comma to search for values at the beginning or end of your text:
你可以，使用 LIKE。您不想匹配部分值，因此您必须在搜索中包含逗号。这也意味着您必须提供一个额外的逗号来搜索文本开头或结尾的值：
select
*
from
YourTable
where
',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'
But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.
但是这个查询会很慢，所有使用 LIKE 的查询都会很慢，尤其是使用前导通配符时。
And there's always a risk. If there are spaces around the values, or values can contain commas themselves in which case they are surrounded by quotes (like in csv files), this query won't work and you'll have to add even more logic, slowing down your query even more.
而且总是有风险。如果值周围有空格，或者值本身可以包含逗号，在这种情况下它们被引号包围（如在 csv 文件中），则此查询将不起作用，您将不得不添加更多逻辑，从而减慢查询速度更。
A better solution would be to add a child table for these categories. Or rather even a separate table for the catagories, and a table that cross links them to YourTable.
更好的解决方案是为这些类别添加一个子表。或者甚至是一个单独的类别表，以及一个将它们交叉链接到 YourTable 的表。
回答by MinhD
You can write a PIPELINED table function which return a 1 column table. Each row is a value from the comma separated string. Use something like this to popa string from the list and putit as a row into the table:
您可以编写一个返回 1 列表的 PIPELINED 表函数。每行都是逗号分隔字符串中的一个值。pop对列表中的字符串使用类似的方法，并将put其作为表中的一行：
PIPE ROW(ltrim(rtrim(substr(l_list, 1, l_idx - 1),' '),' '));
Usage:
用法：
SELECT * FROM MyTable
WHERE 'c2' IN TABLE(Util_Pkg.split_string(categories));
See more here: Oracle docs
在此处查看更多信息：Oracle 文档
回答by Gary_W
For the sake of future searchers, don't forget the regular expression way:
为了将来的搜索者，不要忘记正则表达式方式：
with tbl as (
select 1 ID, 'c1' CATEGORIES from dual
union
select 2 ID, 'c2,c3' CATEGORIES from dual
union
select 3 ID, 'c3,c2' CATEGORIES from dual
union
select 4 ID, 'c3' CATEGORIES from dual
union
select 5 ID, 'c4,c8,c5,c100' CATEGORIES from dual
)
select *
from tbl
where regexp_like(CATEGORIES, '(^|\W)c3(\W|$)');
ID CATEGORIES
---------- -------------
2 c2,c3
3 c3,c2
4 c3
This matches on a word boundary, so even if the comma was followed by a space it would still work. If you want to be more strict and match only where a comma separates values, replace the '\W' with a comma. At any rate, read the regular expression as: match a group of either the beginning of the line or a word boundary, followed by the target search value, followed by a group of either a word boundary or the end of the line.
这在单词边界上匹配，因此即使逗号后跟一个空格它仍然可以工作。如果您想更严格并仅匹配逗号分隔值的位置，请将 '\W' 替换为逗号。无论如何，将正则表达式解读为：匹配一组行首或词边界，后跟目标搜索值，后跟一组词边界或行尾。
回答by David Faber
As long as the comma-delimited list is 512 characters or less, you can also use a regular expression in this instance (Oracle's regular expression functions, e.g., REGEXP_LIKE(), are limited to 512 characters):
只要逗号分隔的列表不REGEXP_LIKE()超过 512 个字符，您也可以在此实例中使用正则表达式（Oracle 的正则表达式函数，例如 ，限制为 512 个字符）：
SELECT id, categories
FROM mytable
WHERE REGEXP_LIKE('c2', '^(' || REPLACE(categories, ',', '|') || ')$', 'i');
In the above I'm replacing the commas with the regular expression alternation operator |. If your list of delimited values is already |-delimited, so much the better.
在上面我用正则表达式替代运算符替换逗号|。如果您的分隔值列表已经是|-delimited，那就更好了。
回答by Yahia
Yes and No...
是与否...
"Yes":
“是的”：
Normalize the data (strongly recommended)- i.e. split the categorie column so that you have each categorie in a separate... then you can just query it in a normal faschion...
规范化数据（强烈推荐）- 即拆分类别列，以便您将每个类别放在一个单独的......然后你可以在正常的faschion中查询它......
"No":
As long as you keep this "pseudo-structure" there will be several issues (performance and others) and you will have to do something similar to:
“不”：
只要你保持这种“伪结构”，就会有几个问题（性能和其他），你将不得不做类似的事情：
SELECT * FROM MyTable WHERE categories LIKE 'c2,%' OR categories = 'c2' OR categories LIKE '%,c2,%' OR categories LIKE '%,c2'
IF you absolutely must you could define a function which is named FIND_IN_SET like the following:
如果你绝对必须，你可以定义一个名为 FIND_IN_SET 的函数，如下所示：
CREATE OR REPLACE Function FIND_IN_SET
( vSET IN varchar2, vToFind IN VARCHAR2 )
RETURN number
IS
rRESULT number;
BEGIN
rRESULT := -1;
SELECT COUNT(*) INTO rRESULT FROM DUAL WHERE vSET LIKE ( vToFine || ',%' ) OR vSET = vToFind OR vSET LIKE ('%,' || vToFind || ',%') OR vSET LIKE ('%,' || vToFind);
RETURN rRESULT;
END;
You can then use that function like:
然后，您可以使用该函数，例如：
SELECT * FROM MyTable WHERE FIND_IN_SET (categories, 'c2' ) > 0;
