分都拿出来了，求这个SQL语句的写法
表名：haha
其中一个字段:test 存的内容如：2002,hhh,zzzzzzzz,qqqqqqqq,777，99999
求一SQL语句将逗号分隔的字符串读出来，且这样显示
test1 test2 test3 test4 test5 test6
2002 hhh zzzzzzzz qqqqqqqq 777 99999
不要用存储过程什么的，我希望能够有SQL自带的方法解决，因为我还要用来绑定网格，谢谢大家指教了。
[解决办法]
Create Table haha
(testVarchar(8000))
Insert haha Select '2002,hhh,zzzzzzzz,qqqqqqqq,777,99999 '
Select
Left(test, CharIndex( ', ', test) - 1) As test1,
Substring(test, CharIndex( ', ', test) + 1, CharIndex( ', ', test, CharIndex( ', ', test) + 1) - CharIndex( ', ', test) - 1) As test2,
Substring(test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) - CharIndex( ', ', test, CharIndex( ', ', test) + 1) - 1) As test3,
Substring(test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1) - CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) - 1) As test4,
Substring(test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1) + 1, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1) + 1) - CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1) - 1) As test5,
Stuff(test, 1, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test, CharIndex( ', ', test) + 1) + 1) + 1) + 1), ' ') As test6
From
haha
GO
Drop Table haha
--Result
/*
test1test2test3test4test5test6
2002hhhzzzzzzzzqqqqqqqq77799999
*/
[解决办法]
Create Table haha
(testVarchar(8000))
Insert haha Select '2002,hhh,zzzzzzzz,qqqqqqqq,777,99999 '
go
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+ 'a ')-2
WHILE @pos> 1 AND CHARINDEX(@split,@s+@split)> 0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen, ' ')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1), ' '))
END
GO
select
test1=dbo.f_GetStr(test,1, ', '),test2=dbo.f_GetStr(test,2, ', '),
test3=dbo.f_GetStr(test,3, ', '),test4=dbo.f_GetStr(test,4, ', '),
test5=dbo.f_GetStr(test,5, ', '),test6=dbo.f_GetStr(test,6, ', ')
from haha
test1 test2 test3 test4 test5 test6
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
2002 hhh zzzzzzzz qqqqqqqq 777 99999
(1 行受影响)
