I have a varchar field that contains a string like "10,11,12,13". How can I use that CSV string to join to another table with those IDs? Here's the开发者_JAVA技巧 approach I'm taking now:
select *
from SomeTable a
WHERE (',' + @csvString + ',') LIKE '%,' + CONVERT(varchar(25), a.ID) + ',%'
Where @csvString is "10,11,12,...". I intend to use this method as a join condition as well.
That method works, but it's rather slow (using CAST doesn't improve the speed).
I understand that having CSVs in the database like that is usually a very silly idea in most cases, but there's nothing I can do about that.
You need a split function. There are many examples of such things on the web. Here's just one: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
There might be a more efficient way to do this with a CTE or Table variable...
CREATE FUNCTION dbo.[SplitIds]
(
@identities varchar(2000)
)
RETURNS
@IdList table
(
ID int
)
AS
BEGIN
DECLARE @ID varchar(10), @idx int
SELECT @identities = LTRIM(RTRIM(@identities))+ ','
SELECT @idx = CHARINDEX(',', @identities, 1)
IF REPLACE(@identities, ',', '') <> ''
BEGIN
WHILE @idx > 0
BEGIN
SELECT @ID = LTRIM(RTRIM(LEFT(@identities, @idx - 1)))
IF @ID <> ''
BEGIN
INSERT INTO @IdList (ID)
VALUES (CAST(@ID AS int))
END
SET @identities = RIGHT(@identities, LEN(@identities) - @idx)
SET @idx = CHARINDEX(',', @identities, 1)
END
END
RETURN
END
GO
DECLARE @string varchar(2000)
SELECT @string = identities FROM MyTable WHERE Id = 1
SELECT *
FROM dbo.People
WHERE Id IN
(
SELECT ID FROM dbo.SplitIds(@string)
)
精彩评论