22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @s varchar(max)
SET @s = '1:abcg,2:cdef,4:tuyys,90:ioijj'
SELECT STUFF((
SELECT ',' + LEFT(v,CHARINDEX(':',v)-1)
FROM (
--BEGIN:
--以下语句可专门用于拆分字符串
SELECT v = x.n.value('.','varchar(100)')
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = @s FOR XML PATH('')),',','</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
--END
) tmp
FOR XML PATH('')
),1,1,'')
if object_id('[f_getstr]') is not null
drop function f_getstr
go
create function f_getstr(@s varchar(1000))
returns varchar(1000)
as
begin
if right(@s,1)<>',' set @s=@s+','
while @s like '%:%,%'
select @s=replace(@s,substring(@s,charindex(':',@s),charindex(',',@s)-charindex(':',@s)+1),';;')
while charindex(';;',@s)>0
select @s=replace(@s,';;',',')
return left(@s,len(@s)-1)
end
go
declare @s varchar(8000)
set @s='1:abcg,2:cdef,4:tuyys,90:ioijj'
select dbo.f_getstr(@s)
/*
1,2,4,90
(1 行受影响)
*/
create table aa_(id int,a varchar(100))
INSERT aa_ SELECT 1,'1:abcg,2:cdef,4:tuyys,90:ioijj'
INSERT aa_ SELECT 2,'33:abcgg,76:cdeghhf,23:tuykkys,2345:ioaasdfijj'
go
CREATE FUNCTION Get_A_(@a VARCHAR(100))
RETURNS VARCHAR(100)
BEGIN
DECLARE @s VARCHAR(100)
DECLARE @t VARCHAR(100)
SET @a=@a+','
WHILE CHARINDEX(',',@a)>0
BEGIN
set @t=LEFT(@a,CHARINDEX(',',@a))
set @s=isnull(@s+',','')+LEFT(@t,CHARINDEX(':',@t)-1)
SET @a=REPLACE(@a,@t,'')
END
RETURN @s
END
SELECT *,dbo.get_A_(a) FROM aa_
/*
id a
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 1:abcg,2:cdef,4:tuyys,90:ioijj 1,2,4,90
2 33:abcgg,76:cdeghhf,23:tuykkys,2345:ioaasdfijj 33,76,23,2345
(所影响的行数为 2 行)
*/
declare @str varchar(300)
declare @r varchar(300)
set @str='1:abcg,2:cdef,4:tuyys,90:ioijj'
declare @i int
declare @len int
set @i = 1
set @r=''
while @i < len(@str+',')
begin
set @r=@r+(substring(@str+',',@i,charindex(',',@str+',',@i)-@i) )
set @r=left(@r,charindex(':',@r)-1)+','
set @i = charindex(',',@str+',',@i)+1
end
select left(@r,len(@r)-1)
/*
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,4,90
(所影响的行数为 1 行)
*/
create table #t(
id varchar(10))
declare @str varchar(300)
set @str='12,13,14,20'
declare @i int
declare @len int
set @i = 1
while @i < len(@str+',')
begin
insert #t select substring(@str+',',@i,charindex(',',@str+',',@i)-@i)
set @i = charindex(',',@str+',',@i)+1
end
select * from #t
/*
id
----------
12
13
14
20
(所影响的行数为 4 行)
*/
--drop table #t