declare @tsh2 varchar(800) set @tsh2='1,2,3,4,5,6'SET @tsh2='SELECT '+REPLACE(@tsh2,',',' union all select ')INSERT INTO 表(列) EXEC(@tsh2)
declare @tsh2 varchar(800) set @tsh2='1,2,3,4,5,6'SET @tsh2 = @tsh2+',' ;WITH a(i,rn) AS ( SELECT number, ROW_NUMBER() OVER(ORDER BY number) FROM master..spt_values WHERE type = 'p' AND SUBSTRING(@tsh2,number,1) = ',' ), b(rn,i1,i2) AS ( SELECT a1.rn, ISNULL(a0.i,0)+1, a1.i FROM a a1 LEFT JOIN a a0 ON a0.rn = a1.rn - 1 ) SELECT SUBSTRING(@tsh2,i1,i2-i1) tsh2 FROM b tsh2 ---- 1 2 3 4 5 6
可以写个分隔字符串的方法: CREATE FUNCTION dbo.f_splitstr(@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100)) RETURNS @temp TABLE(F1 VARCHAR(100)) AS BEGIN DECLARE @ch AS VARCHAR(100) SET @SourceSql=@SourceSql+@StrSeprate WHILE(@SourceSql<>'') BEGIN SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1) INSERT @temp VALUES(@ch) SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'') END RETURN END GO然后调用: declare @tsh2 varchar(800) set @tsh2='1,2,3,4,5,6' SELECT * FROM dbo.f_splitstr(@tsh2,',')结果:
set @tsh2='1,2,3,4,5,6'SET @tsh2='SELECT '+REPLACE(@tsh2,',',' union all select ')INSERT INTO 表(列)
EXEC(@tsh2)
set @tsh2='1,2,3,4,5,6'SET @tsh2 = @tsh2+','
;WITH a(i,rn) AS (
SELECT number,
ROW_NUMBER() OVER(ORDER BY number)
FROM master..spt_values
WHERE type = 'p'
AND SUBSTRING(@tsh2,number,1) = ','
),
b(rn,i1,i2) AS (
SELECT a1.rn,
ISNULL(a0.i,0)+1,
a1.i
FROM a a1
LEFT JOIN a a0
ON a0.rn = a1.rn - 1
)
SELECT SUBSTRING(@tsh2,i1,i2-i1) tsh2
FROM b
tsh2
----
1
2
3
4
5
6
CREATE FUNCTION dbo.f_splitstr(@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100))
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
SET @SourceSql=@SourceSql+@StrSeprate
WHILE(@SourceSql<>'')
BEGIN
SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)
INSERT @temp VALUES(@ch)
SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')
END
RETURN
END
GO然后调用:
declare @tsh2 varchar(800)
set @tsh2='1,2,3,4,5,6'
SELECT * FROM dbo.f_splitstr(@tsh2,',')结果: