-- ============================================= -- Author: cc -- Create date: 2010-12-16 -- Description: 拆分字符串 返回表 -- ============================================= CREATE FUNCTION [dbo].[fSplitTb] ( @SQL NVARCHAR(MAX), -- 传入的字符串 @CHAR CHAR(1) -- 分割的单个字符 ) RETURNS @SplitTb TABLE ( cValue NVARCHAR(200) ) AS BEGIN DECLARE @NUM INT,@INDEX INT; SET @NUM=1; SET @INDEX=0; SET @SQL=@SQL+@CHAR; WHILE CHARINDEX(@CHAR,@SQL,@NUM)>0 -- 循环遍历 BEGIN IF @INDEX<>@NUM BEGIN SET @NUM=CHARINDEX(@CHAR,@SQL,@NUM) IF LEN(SUBSTRING(@SQL,@INDEX+1,@NUM-@INDEX-1))>0 BEGIN INSERT INTO @SplitTb SELECT SUBSTRING(@SQL,@INDEX+1,@NUM-@INDEX-1); END SET @INDEX=@NUM; SET @NUM=@NUM+1; END END RETURN; END select * from table A INNER JOIN [dbo].[fSplitTb]('1,2,3,4',',') B ON A.ID=B.cValue
在链接查询 inner join
-- =============================================
-- Author: cc
-- Create date: 2010-12-16
-- Description: 拆分字符串 返回表
-- =============================================
CREATE FUNCTION [dbo].[fSplitTb]
(
@SQL NVARCHAR(MAX), -- 传入的字符串
@CHAR CHAR(1) -- 分割的单个字符
)
RETURNS @SplitTb TABLE
(
cValue NVARCHAR(200)
)
AS
BEGIN
DECLARE @NUM INT,@INDEX INT;
SET @NUM=1;
SET @INDEX=0;
SET @SQL=@SQL+@CHAR;
WHILE CHARINDEX(@CHAR,@SQL,@NUM)>0 -- 循环遍历
BEGIN
IF @INDEX<>@NUM
BEGIN
SET @NUM=CHARINDEX(@CHAR,@SQL,@NUM)
IF LEN(SUBSTRING(@SQL,@INDEX+1,@NUM-@INDEX-1))>0
BEGIN
INSERT INTO @SplitTb SELECT SUBSTRING(@SQL,@INDEX+1,@NUM-@INDEX-1);
END
SET @INDEX=@NUM;
SET @NUM=@NUM+1;
END
END
RETURN;
END
select * from table A INNER JOIN [dbo].[fSplitTb]('1,2,3,4',',') B
ON A.ID=B.cValue