字符串转换为结果集？

[7年]

请教各位大大？如何将
'01,02,03,04' 转换为1列的结果集
01
02
03
04

目前使用函数可以办到，但是我想在存储过程中实现，请各位帮忙

[百年树人]

declare @s varchar(100)
set @s='01,02,03,04'
set @s=' select '''+replace(@s,',',''' as col union all select ''')+''''
exec (@s)

[我腫了]

USE test
GO


DECLARE	@s NVARCHAR(MAX),@Sql NVARCHAR(MAX)

SET @s='01,02,03,04'


SET @Sql='Select '''+REPLACE(@s,',','''	Union all Select ''')+''''


EXEC ( @Sql )

[發糞塗牆]

 ALTER function [dbo].[fnSys_SplitClass]
 (
 	---字符串分割
 	@ClassSql varchar(max), --传入的字符串
 	@StrSeprate varchar(10)--分隔符
 )
 Returns @temp table(Class varchar(100)) --返回一个Table
 As 
 Begin
 	Declare @i int
 	Set @ClassSql =rtrim(ltrim(@ClassSql ))
 	Set @i=charindex(@StrSeprate,@ClassSql )
 	While @i>=1
 	Begin
 		Insert @temp values(left(@ClassSql ,@i-1))
 		Set @ClassSql =substring(@ClassSql ,@i+1,len(@ClassSql )-@i)
 		Set @i=charindex(@StrSeprate,@ClassSql )
 	End
 	If @ClassSql <>'' 
 		Insert @temp values(@ClassSql )
 	Return 
 End
 

[dodolzc10]

[Quote=引用 2 楼 的回复:]
SQL code


USE test
GO


DECLARE @s NVARCHAR(MAX),@Sql NVARCHAR(MAX)

SET @s='01,02,03,04'


SET @Sql='Select '''+REPLACE(@s,',',''' Union all Select ''')+''''


EXEC ( @Sql )

……
[/Quote]
能解释下那些'''号吗

[我腫了]

[Quote=引用 4 楼 的回复:]

引用 2 楼 的回复:
SQL code


USE test
GO


DECLARE @s NVARCHAR(MAX),@Sql NVARCHAR(MAX)

SET @s='01,02,03,04'


SET @Sql='Select '''+REPLACE(@s,',',''' Union all Select ''')+''''


EX……
[/Quote]

字符串內的兩個引號 '' 等於 串外的一個引號 '

[dodolzc10]

[Quote=引用 5 楼 的回复:]
引用 4 楼 的回复:

引用 2 楼 的回复:
SQL code


USE test
GO


DECLARE @s NVARCHAR(MAX),@Sql NVARCHAR(MAX)

SET @s='01,02,03,04'


SET @Sql='Select '''+REPLACE(@s,',',''' Union all Select ''')+''''
……
[/Quote]
学习了,谢谢

[DBA_磊仔]

N种方法

/* 拆分字符串   
* */ 
--拆分单列+序号
--方法一：利用数字辅助表
if object_id('fn_SplitTSQL')is not null drop function  fn_SplitTSQL
GO
create FUNCTION dbo.fn_SplitTSQL
  (@s NVARCHAR(MAX), @split NCHAR(1)) RETURNS TABLE
AS
RETURN
  SELECT
    n - LEN(REPLACE(LEFT(array, n), @split, '')) + 1 AS rn,
    SUBSTRING(array, n,
      CHARINDEX(@split, array + @split, n) - n) AS col
  FROM (SELECT @s AS array) AS D
    JOIN dbo.Nums
      ON n <= LEN(array)
      AND SUBSTRING(@split + array, n, 1) = @split;
GO

--方法二：直接拆分
if object_id('f_split')is not null drop function  f_split
go
create function f_split
(
@s     varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)returns table
as
 return
 (
  select Row_number()over(order by Number) rn ,
  substring(@s,number,charindex(@split,@s+@split,number)-number)as col
  from master..spt_values
  where type='p' and number<=len(@s+'a') 
  and charindex(@split,@split+@s,number)=number
  )
go

select * from dbo.f_split('11,2,3',',')
select * from dbo.fn_SplitTSQL('11,2,3',',')
GO

--拆分多列
if object_id('fn_MutiSplitTSQL')is not null drop function  fn_MutiSplitTSQL
GO
create FUNCTION dbo.fn_MutiSplitTSQL  
  (@s NVARCHAR(MAX), @split NCHAR(1) ,@Sub@split NCHAR(1)= N',') RETURNS TABLE  
AS  
RETURN
  select * from (SELECT d.rn ,'col'+ cast( n - LEN(REPLACE(LEFT(col, n), @Sub@split, '')) + 1 as varchar(10)) AS attribute,  
    SUBSTRING(col, n, CHARINDEX(@Sub@split, col + @Sub@split, n) - n) AS value  
  FROM (  SELECT  n - LEN(REPLACE(LEFT(array, n), @split, '')) + 1 AS rn,  
    SUBSTRING(array, n, CHARINDEX(@split, array + @split, n) - n) AS col  
  FROM (SELECT @s AS array) AS D  
    JOIN dbo.Nums a 
      ON n <= LEN(array)  
     AND SUBSTRING(@split + array, n, 1) = @split ) AS D  
    JOIN dbo.Nums a 
      ON n <= LEN(col)  
     AND SUBSTRING(@Sub@split + col, n, 1) = @Sub@split ) as d
    PIVOT(max(value) FOR attribute 
						IN(col1,col2 ,col3,col4,col5 )) AS P  
GO

 select col1,col2,col3,col4
	from dbo.fn_MutiSplitTSQL
	('$092-1350,099201-080901,12050720,2012-6-11$092-0970,099204-072301,12050734,2012-6-11$','$',',')   
GO

[7年]

实在不好意思，最近没上网，谢谢各位的回答！结贴了。