是否可以查询特定值的逗号分隔列？ [英] Is it possible to query a comma separated column for a specific value?

问题描述

我有（而且不拥有，所以我不能改变）一个表格与类似的布局。

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

我需要返回包含特定类别ID的行。我开始用LIKE语句写查询，因为值可以在字符串中的任何位置。

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

SELECT id FROM table WHERE类别LIKE'％c2 ％';
返回第2行和第3行

SELECT id FROM table WHERE categories LIKE '%c2%'; Would return rows 2 and 3

SELECT id FROM table WHERE类别LIKE' c3％'和类别LIKE'％c2％'; 会再次找到第2行和第3行，但不行第4行

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％'or 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

我不喜欢所有 LIKE 语句。我在Oracle文档中找到了 FIND_IN_SET（），但它似乎在10g中不起作用。我收到以下错误：

I don't like all the LIKE statements. I've found FIND_IN_SET() in the Oracle documentation but it doesn't seem to work in 10g. I get the following error:

ORA-00904: "FIND_IN_SET": invalid identifier
00904. 00000 -  "%s: invalid identifier"

： SELECT id FROM table WHERE FIND_IN_SET（'c2'，categories）; （来自文档的示例）或此查询： SELECT id FROM table WHERE FIND_IN_SET（'c2'，categories）< 0; （来自Google的示例）

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)

我希望它返回第2行和第3行。

I would expect it to return rows 2 and 3.

有没有更好的方式来写这些查询，而不是使用大量的 LIKE 语句？

Is there a better way to write these queries instead of using a ton of LIKE statements?

推荐答案

可以使用LIKE。您不希望匹配部分值，因此您必须在搜索中包含逗号。这也意味着你必须提供一个额外的逗号，在文本的开头或结尾搜索值：

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:

select 
  * 
from
  YourTable 
where 
  ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'

但这个查询会很慢，使用LIKE进行查询，特别是使用领先的通配符。

But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.

总有一个风险。如果值周围有空格，或者值可以包含逗号，在这种情况下，它们被引号包围（例如在csv文件中），则此查询将不起作用，您必须添加更多逻辑，从而减慢查询甚至更多。

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.

更好的解决方案是为这些类别添加子表。或者甚至一个单独的表的表，以及一个表，将它们交叉链接到YourTable。

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.

这篇关于是否可以查询特定值的逗号分隔列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！