27,579
社区成员
发帖
与我相关
我的任务
分享
declare @str nvarchar(20)
set @str = '1;2,3,12,13'
declare @s nvarchar(20)
set @s = ',' + @str + ','
select @s = replace(replace(@s,';',','),','+F1+',',',') from split('1,3',',')
select @s
DECLARE @str VARCHAR(100)
SET @str='1,2,3;12;13'----输入你的字符串
;WITH CTE
AS
(
SELECT b.col
FROM (SELECT col=CAST('<v>'+replace(REPLACE(@str,';','</v><v>'),',','</v><v>')+'</v>' AS xml)) a---多个不同字符串在这里处理,可以外层再套一层replace处理就可以了
OUTER APPLY (SELECT col=T.C.value('.','varchar(100)') FROM a.col.nodes('/v') AS T(C)) b
)
SELECT Col=STUFF((SELECT ',' + col FROM CTE C
WHERE c.col not in(select id FROM #tp)
FOR XML PATH('')),1,1,'')
Col
----------------------
2,12,13
(1 row(s) affected)
CREATE TABLE #tp
(
id INT
)
INSERT INTO #tp select 1
INSERT INTO #tp select 3
DECLARE @str VARCHAR(10)
SET @str='1,2,3,12,13'----输入你的字符串
;WITH CTE
AS
(
SELECT b.col
FROM (SELECT col=CAST('<v>'+REPLACE(@str,',','</v><v>')+'</v>' AS xml)) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b
)
SELECT Col=STUFF((SELECT ',' + col FROM CTE C
WHERE c.col not in(select id FROM #tp)
FOR XML PATH('')),1,1,'')
Col
--------------------------
2,12
(1 row(s) affected)
select @s = replace(@s,','+F1+',',',') from split('1,3',',')
select @s = replace(@s,';'+F1+';',',') from split('1,3',',')
select @s = replace(@s,','+F1+';',',') from split('1,3',',')
select @s = replace(@s,';'+F1+',',',') from split('1,3',',')
select @s
create function split
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select F1 from split('1,3',',')
---结果
F1
---------------
1
3
declare @str nvarchar(20)
set @str = '1,2,3,12,13'
declare @s nvarchar(20)
set @s = ',' + @str + ','
select @s = replace(@s,','+F1+',',',') from split('1,3',',')
select @s
--------------------
,2,12,13,