34,593
社区成员
发帖
与我相关
我的任务
分享
DECLARE @arr VARCHAR(128)
DECLARE @Tlist1 VARCHAR(64)
DECLARE @posStart INT
DECLARE @posCur INT
DECLARE @TID INT
SET @posStart = 1
SET @posCur = 1
SET @arr = '4,401,1;4,402,2;4,403,3;4,404,4'
SET @posCur = CHARINDEX(';',@arr,@posStart)
set @Tlist1=cast (SUBSTRING(@arr,@posStart ,@posCur-@posStart) as varchar(64))
PRINT @Tlist1
--二次拆分 4,401,1 拆分为4 401 1
SET @posStart = @posCur
WHILE (@posStart+1 < LEN(@arr))
Begin
Set @posCur=CharIndex( ';',@arr, @posStart+1)
if(@posCur> 0)
Begin
set @Tlist1=cast (SUBSTRING(@arr,@posStart+1 ,@posCur-@posStart-1) as varchar(64))
PRINT @Tlist1
--二次拆分
SET @posStart = @posCur
End
else
Break
End
set @Tlist1=cast (SUBSTRING(@arr,@posStart+1 ,LEN(@arr)-@posStart+1) as varchar(64))
PRINT @Tlist1
--二次拆分
--如果没有小数,可以把逗号改一下,
declare @s nvarchar(100)
declare @split nvarchar(10)
set @s='4,401,1;4,402,2;4,403,3;4,404,4'
set @split=';'
set @s=REPLACE (@s,',','.')
select PARSENAME(item,3) as ColA,
PARSENAME(item,2) as ColB,
PARSENAME(item,1) as ColC
from dbo.Fun_String2ToStringArray(@s,@split)
declare @list varchar(50)
set @list=''
while 1=1
begin
/*一级拆分*/
select top 1 @list=item from (select item from dbo.Fun_String2ToStringArray('4,401,1;4,402,2;4,403,3;4,404,4',';')as list where item>@list order by item
if @@ROWCOUNT=0
break
/*二级拆分*/
select item from dbo.Fun_String2ToStringArray(@list,',')
end
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END