在子查询中返回逗号分隔的列表的SQL

Say I have a field in a SQL table (Locations) whose value is a semi-colon separated list:

假设我在SQL表(位置)中有一个字段，它的值是分号列表:

username1;username2;username3

If I say:

如果我说:

SELECT '''' + REPLACE(owners, ';', ''',''') + '''' FROM Locations WHERE ...

I can get it to output:

我可以把它输出:

'username1','username2','username3'

... but what I want to do is then use that in a subquery:

…但是我想做的是在子查询中使用这个

SELECT * FROM Users WHERE UserName IN (   
    SELECT '''' + REPLACE(owners, ';', ''',''') + '''' 
    FROM Locations WHERE ...
)

So, essentially I want the output of that subquery to act as a string in the IN() clause. Is this possible?

本质上，我希望子查询的输出作为in()子句中的字符串。这是可能的吗?

Thanks!

谢谢!

2 个解决方案

#1

No you can't do what you are trying to do, construct 'username1','username2','username3' at runtime.

不，你不能做你想做的事，在运行时创建username1, username2, username3。

yet a simpler solution would be to use a split function

然而，一个更简单的解决方案是使用拆分函数。

Split function

CREATE FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) 
RETURNS @t TABLE (id int identity(1,1), val nvarchar(max))
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

Query

SELECT *
FROM [Users] u 
WHERE u.UserName IN (  SELECT val 
                       FROM [Location] l
                                    CROSS APPLY [dbo].[Split](l.owners, ';')
                    );

#2

If the inner select returns only one row you can use CHARINDEX:

如果内部选择只返回一行，则可以使用CHARINDEX:

SELECT * FROM Users WHERE CHARINDEX(''''+UserName+'''', (
   SELECT '''' + REPLACE(owners, ';', ''',''') + '''' 
   FROM Locations WHERE ...
)) > 0