求SQL语句

[Johan.]

下面有这样一条数据
13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf

要得到的结果是：
13 32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5
1795 fd892060-9b94-49c3-b1c9-b435342f0caf

请问该怎么写呢？

谢啦。

[修改一下昵称]

DECLARE @strs varchar(1000)
SET @strs = '13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf'
select substring(@strs,1,2) as a,substring(@strs,4,charindex(',',@strs)-4) as b
union all
select substring(@strs,charindex(',',@strs)+1,4) as a,substring(@strs,charindex(',',@strs)+6,10000) as b
/*
a        b
-------- -------------------------------------
13       32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5
1795     fd892060-9b94-49c3-b1c9-b435342f0caf

(2 行受影响)
*/

[obuntu]

declare @str 
varchar(200)='13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf'

declare @leftstr varchar(200)
declare @rightstr varchar(200)
select @leftstr=SUBSTRING(@str,1,CHARINDEX(',',@str,1)-1),
	   @rightstr=SUBSTRING(@str,CHARINDEX(',',@str,1)+1,LEN(@str))	
	   
select SUBSTRING(@leftstr,1,CHARINDEX('#',@leftstr,1)-1),
	   SUBSTRING(@leftstr,CHARINDEX('#',@leftstr,1)+1,len(@leftstr))
union all
select SUBSTRING(@rightstr,1,CHARINDEX('#',@rightstr,1)-1),
	   SUBSTRING(@rightstr,CHARINDEX('#',@rightstr,1)+1,len(@rightstr))

[Johan.]

要是后面还有呢？

13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf,13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf,........

该怎么查呢？

[dawugui]

[Quote=引用 3 楼 s2t11enterprise 的回复:]
要是后面还有呢？

13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf,13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf,........

该怎么查呢？
[/Quote]

/*
功能：实现split功能的函数
*/

create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as 

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return 
end
go

--调用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split 

/*
a                     
----------------------
1
2
3
4
5
6
7
8
55

（所影响的行数为 9 行）
*/

[永生天地]

CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    DECLARE @splitlen int
    SET @splitlen=LEN(@split+'a')-2
    WHILE CHARINDEX(@split,@s)>0
    BEGIN
        INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    END
    INSERT @re VALUES(@s)
    RETURN
END
GO

select left(col,charindex('#',col)-1) x1,stuff(col,1,charindex('#',col),'') x2
from dbo.f_splitSTR('13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf,13#32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5,1795#fd892060-9b94-49c3-b1c9-b435342f0caf',',') b
/*
x1                                                                                                   x2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13                                                                                                   32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5
1795                                                                                                 fd892060-9b94-49c3-b1c9-b435342f0caf
13                                                                                                   32c8ad22-9136-4fd2-b0c5-eadd9ef4e0d5
1795                                                                                                 fd892060-9b94-49c3-b1c9-b435342f0caf

(4 行受影响)

*/