1.Arithmetic one:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[fn_SplitToTable]
(
@sText varchar ( 8000 ) ,
@sDelim varchar ( 20 ) = ' '
)
/*
调用示例：SELECT * FROM dbo.fn_SplitToTable('aa&&&&1bb&&&&1cc','&&&&1')
*/
RETURNS @retArray TABLE
(
idx smallint PRIMARY KEY ,
value varchar ( 8000 )
)
AS
BEGIN
DECLARE @idx smallint ,
@value varchar ( 8000 ) ,
@bcontinue bit ,
@iStrike smallint ,
@iDelimlength tinyint
IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END
SET @idx = 0
SET @sText = ltrim ( rtrim ( @sText ) )
SET @iDelimlength = datalength ( @sDelim )
SET @bcontinue = 1
IF NOT
( ( @iDelimlength = 0 ) OR ( @sDelim = 'Empty' ) )
BEGIN
WHILE @bcontinue = 1
BEGIN
IF charindex ( @sDelim , @sText ) > 0
BEGIN
SET @value = substring ( @sText , 1 , charindex ( @sDelim , @sText ) - 1 )
BEGIN
INSERT @retArray ( idx , value )
VALUES ( @idx , @value )
END
SET @iStrike = datalength ( @value ) + @iDelimlength
SET @idx = @idx + 1
SET @sText = ltrim ( right ( @sText , datalength ( @sText ) - @iStrike ) )
END
ELSE
BEGIN
SET @value = @sText
BEGIN
INSERT @retArray ( idx , value )
VALUES ( @idx , @value )
END
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue = 1
BEGIN
IF datalength ( @sText ) > 1
BEGIN
SET @value = substring ( @sText , 1 , 1 )
BEGIN
INSERT @retArray ( idx , value )
VALUES ( @idx , @value )
END
SET @idx = @idx + 1
SET @sText = substring ( @sText , 2 , datalength ( @sText ) - 1 )
END
ELSE
BEGIN
INSERT @retArray ( idx , value )
VALUES ( @idx , @sText )
SET @bcontinue = 0
END
END
END
RETURN
END
2.Arithmetic two:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Function [dbo].[SpliteStringToListByParam](
@strings varchar(max),
@sp VARCHAR(10)
)
/*
调用示例：SELECT * FROM [SpliteStringToListByParam]('aaa@@@bbb','@@@')
*/
Returns @ReturnTable Table(idx int IDENTITY(1,1) Primary Key,ID VARCHAR(max))
As
BEGIN
DECLARE @len INT
SET @len=len(@sp)+1
Insert @ReturnTable
select substring(c,@len,charindex(@sp,c,@len)-@len) as empno from
(
select substring(csv.emps,iter.pos,len(csv.emps)) as c from
(
select @sp+@strings+@sp as emps
) csv,
(
--select id as pos from t100
--生产1-10000的结果集
Select a + b * 10 +c*100+d*1000+1 as pos From
(Select 0 As a Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9 ) A,
(Select 0 As b Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9 ) B,
(Select 0 As c Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9 ) c,
(Select 0 As d Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9 ) d
) iter where iter.pos <= len(csv.emps)
)x
where len(c) > len(@sp) and substring(c,1,len(@sp)) = @sp
Return
END