SQL自定义函数split 将数组(分隔字符串)返回阵列(表)
来源:互联网 发布:淘宝男模特汪逸轩 编辑:程序博客网 时间:2024/06/07 01:26
SQL自定义函数split
Create Function Split(@Strs As Nvarchar(1024),@Separator as Nvarchar(10),@Index as Int)
Returns Nvarchar(1024) As
begin
Declare @i As Int, @charpos As Nvarchar(1024)
Set @charpos = @Strs
Set @i = 1
If @Index < 0
Begin
Set @charpos = '超出下界'
End
Else
Begin
While @i <= (@Index - 1)
Begin
If CharIndex(@Separator, @charpos) > 0
Begin
Set @charpos = Substring(@charpos, CharIndex(@Separator, @charpos) + 1, Len(@charpos) - CharIndex(@Separator, @charpos))
End
Else
Begin
Set @charpos = '超出上界'
Break
End
Set @i = @i + 1
End
If @charpos <> '超出上界'
Begin
If CharIndex(@Separator, @charpos) > 0
Begin
Set @charpos = Left(@charpos, CharIndex(@Separator, @charpos) - 1)
End
End
End
Return @charpos
End
--调用
select dbo.Split('sdf|abc|csc|aldsfj|sfj|取出原素|asdf|adf|...','|',6)
--返回 取出原素
Returns Nvarchar(1024) As
begin
Declare @i As Int, @charpos As Nvarchar(1024)
Set @charpos = @Strs
Set @i = 1
If @Index < 0
Begin
Set @charpos = '超出下界'
End
Else
Begin
While @i <= (@Index - 1)
Begin
If CharIndex(@Separator, @charpos) > 0
Begin
Set @charpos = Substring(@charpos, CharIndex(@Separator, @charpos) + 1, Len(@charpos) - CharIndex(@Separator, @charpos))
End
Else
Begin
Set @charpos = '超出上界'
Break
End
Set @i = @i + 1
End
If @charpos <> '超出上界'
Begin
If CharIndex(@Separator, @charpos) > 0
Begin
Set @charpos = Left(@charpos, CharIndex(@Separator, @charpos) - 1)
End
End
End
Return @charpos
End
--调用
select dbo.Split('sdf|abc|csc|aldsfj|sfj|取出原素|asdf|adf|...','|',6)
--返回 取出原素
将数组(分隔字符串)返回阵列(表)
--将数组(分隔字符串)返回阵列(表)
--drop function fn_Split
--自定义函数
CREATE FUNCTION fn_Split(@sText nvarchar(4000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value nvarchar(4000),
@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 you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value) VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
if @idx=12
begin
set @bcontinue=0
end
SET @sText = LTrim(right(@sText,(DATALENGTH(@sText) - @iStrike)/2))
END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
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
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value) VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END
--测试
declare @no char(100)
set @no='china 中国,%…-- desefd,e 中国人fddc,mgns,a a'
select * from fn_Split(@no,',')
--drop function fn_Split
--自定义函数
CREATE FUNCTION fn_Split(@sText nvarchar(4000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value nvarchar(4000),
@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 you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value) VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
if @idx=12
begin
set @bcontinue=0
end
SET @sText = LTrim(right(@sText,(DATALENGTH(@sText) - @iStrike)/2))
END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
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
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value) VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END
--测试
declare @no char(100)
set @no='china 中国,%…-- desefd,e 中国人fddc,mgns,a a'
select * from fn_Split(@no,',')
--返回结果
idx [value]
0 china 中国
1 %…-- desefd
2 e 中国人fddc
3 mgns
4 a a
上面的判断太多了不易于理解,下面转了一个简单的意思基本相同代码简单很多
-- =============================================
-- Author: 阿瑞
-- Create date: 2008-03-19
-- Description: split函数
-- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
RETURNS
@TEMP_Table TABLE (a varchar(100))
AS
BEGIN
DECLARE @i int
SET @SourceSql=rtrim(ltrim(@SourceSql))
SET @i=charindex(@StrSeprate,@SourceSql)
WHILE @i>=1
BEGIN
INSERT @TEMP_Table VALUES(left(@SourceSql,@i-1))
SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
SET @i=charindex(@StrSeprate,@SourceSql)
END
if @SourceSql<>'/'
INSERT @TEMP_Table values(@SourceSql)
RETURN
END
-- Author: 阿瑞
-- Create date: 2008-03-19
-- Description: split函数
-- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
RETURNS
@TEMP_Table TABLE (a varchar(100))
AS
BEGIN
DECLARE @i int
SET @SourceSql=rtrim(ltrim(@SourceSql))
SET @i=charindex(@StrSeprate,@SourceSql)
WHILE @i>=1
BEGIN
INSERT @TEMP_Table VALUES(left(@SourceSql,@i-1))
SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
SET @i=charindex(@StrSeprate,@SourceSql)
END
if @SourceSql<>'/'
INSERT @TEMP_Table values(@SourceSql)
RETURN
END
调用:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
结果:
- SQL自定义函数split 将数组(分隔字符串)返回阵列(表)
- SQL自定义函数split 将数组(分隔字符串)返回阵列(表)
- SQL自定义函数split 将数组(分隔字符串)返回阵列(表)
- SQL自定义函数split分隔字符串
- 定义自定义表值函数(split),用于分隔字符串
- 用Split函数分隔字符串
- Oracle split(分隔字符串函数)
- oracle字符串分隔函数split
- 一个扩展后的Split方法,可根据字符串分隔字符,返回字符数组
- sql 字符串分隔函数
- sql字符串分隔函数
- Oracle 实现 split 函数 (返回数组)
- oracle 实现 split 函数 (返回数组)
- oracle 实现 split 函数 (返回数组)
- oracle 实现 split 函数 (返回数组)
- Delphi字符串分隔(似split函数功能)
- SQL 自定义Split函数
- SQL 自定义Split函数
- 要工作了
- 网站上“增加有事Q我”以及MSN
- 找出并统计任意字符串中出现最多的字符
- 记忆棒-4G的高速棒居然比2G的高速棒慢一截
- 大字节序VS小字节序
- SQL自定义函数split 将数组(分隔字符串)返回阵列(表)
- 牢记!SQL Server数据库开发的二十一条军规
- Visual C++设计超强仿QQ自动伸缩窗口
- 刷机-新版PSP2000的自制系统3.60M33刷到3.71M33的刷机教程
- 针对vb中scriptcontrol 数组调用另类解决思路
- 《oracle pl/sql programming》 目录
- *.lwp文件如何打开
- 《oracle pl/sql programming》 第一章 介绍 第二章 sql*plus
- 通过javascript操作web控件的自定义属性