SQL Server 2008如何从一列中查询逗号分隔的字符串？

Table POST:

表POST：

postId  Tag    Author
-----------------------
1       A,B    x,y
2       B,C,D  x
3       C,D    y

Questions:

问题：

How to select records that have Tag='B' in the comma separated string? From the above example it should return record 1,2.

如何在逗号分隔的字符串中选择Tag ='B'的记录？从上面的例子中，它应该返回记录1,2。
How to select records that have Tag='B' and Author='y'? The returned records should be 1.

如何选择Tag ='B'和Author ='y'的记录？返回的记录应为1。

BTW: if I redesign database scheme into three tables POST, TAG and AUTHOR, then query the desirable records by inner join clause. Compared with the former method, which is better in performance? The table POST has 10 thousand records in total.

顺便说一句：如果我将数据库方案重新设计成三个表POST，TAG和AUTHOR，那么通过内连接子句查询所需的记录。与前一种方法相比，哪种性能更好？表POST总共有1万条记录。

2 个解决方案

#1

Obviously, you know that storing lists as delimited strings is the wrong way to store values in SQL. Sometimes, we are stuck with other people's really bad designs, but the first step should really be to fix the data model.

显然，您知道将列表存储为分隔字符串是在SQL中存储值的错误方法。有时，我们会遇到其他人非常糟糕的设计，但第一步应该是修复数据模型。

To do what you want, you can do:

要做你想做的事，你可以做：

select *
from t
where ',' + tag + ',' like '%,B,%'

The second question is similar:

第二个问题是类似的：

where ',' + tag + ',' like '%,B,%' and
      ',' + author + ',' like '%,y,%'

#2

How about using charindex?

如何使用charindex？

select * from table1 where charindex('B', tag) <> 0;

select * from table1 where charindex('B', tag) <> 0 and charindex('y', author) <> 0;

Fiddle.

小提琴。

#1