所以要本身来促成了,代码如下复制代码

以前做数据切割时我们都用php或asp来实现,今天突然发现sql中的Split函数也可以实现在sql查询中实现切割字符串哦,有需要的朋友可以参考一下。

原文地址:

在sql中没有系统带的的字符分割函数,我们利用了一些功能字了一个分割函数有需要的同学可以参考一下。
代码如下复制代码

复制代码 代码如下: SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO /* by kudychen 2011-9-28 */ CREATE
function [dbo].[SplitString] , –input string to be separated
@Separator nvarchar=’,’, –a string that delimit the substrings in the
input string @RemoveEmptyEntries bit=1 –the return value does not
include array elements that contain an empty string ) returns @TABLE
table ( [Id] int identity, [Value] nvarchar as begin declare @Index
int, @Entry nvarchar set @Index = charindex while begin set
@Entry=ltrim(rtrim(substring) if (@RemoveEmptyEntries=0) or
(@RemoveEmptyEntries=1 and @Entry<>”) begin insert into @TABLE
Values end set @Input = substring(@Input, @Index+datalength/2, len set
@Index = charindex end set @Entry=ltrim if (@RemoveEmptyEntries=0) or
(@RemoveEmptyEntries=1 and @Entry<>”) begin insert into @TABLE
Values end return end 如何使用: 复制代码
代码如下: declare @str1 varchar, @str2 varchar, @str3 varchar set @str1
= ‘1,2,3’ set @str2 = ‘1###2###3’ set @str3 =
‘1###2###3###’ select [Value] from [dbo].[SplitString]
select [Value] from [dbo].[SplitString] select [Value] from
[dbo].[SplitString] 执行结果:

有时我们要用到批量操作时都会对字符串进行拆分,可是SQL
Server中却没有自带Split函数,所以要自己来实现了。没什么好说的,需要的朋友直接拿去用吧

首先创建一个表值函数:

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

里面还有个自增的[Id]字段哦,在某些情况下有可能会用上的,例如根据Id来保存排序等等。
例如根据某表的ID保存排序: 复制代码
代码如下:update a set a.[Order]=t.[Id] from [dbo].[表] as a join
[dbo].SplitString as t on a.[Id]=t.[Value]
具体的应用请根据自己的情况来吧:)作者:Kudy

代码如下复制代码

 

/*by kudychen 2011-9-28 */CREATE function [dbo].[SplitString](
@Input nvarchar(max), –input string to be separated @Separator
nvarchar(max)=’,’, –a string that delimit the substrings in the input
string @RemoveEmptyEntries bit=1 –the return value does not include
array elements that contain an empty string)returns @TABLE table (
[Id] int identity(1,1), [Value] nvarchar(max)) asbegin declare
@Index int, @Entry nvarchar(max) set @Index =
charindex(@Separator,@Input)

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
/* 
by kudychen 2011-9-28 
*/ 
CREATE function [dbo].[SplitString] 

@Input nvarchar(max), –input string to be separated 
@Separator nvarchar(max)=’,’, –a string that delimit the substrings in the input string 
@RemoveEmptyEntries bit=1 –the return value does not include array elements that contain an empty string 

returns @TABLE table 

[Id] int identity(1,1), 
[Value] nvarchar(max) 

as 
begin 
declare @Index int, @Entry nvarchar(max) 
set @Index = charindex(@Separator,@Input) 
while (@Index>0) 
begin 
set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1))) 
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>”) 
begin 
insert into @TABLE([Value]) Values(@Entry) 
end 
set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input)) 
set @Index = charindex(@Separator, @Input) 
end 
set @Entry=ltrim(rtrim(@Input)) 
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>”) 
begin 
insert into @TABLE([Value]) Values(@Entry) 
end 
return 
end

while (@Index0) begin set @Entry=ltrim(rtrim(substring(@Input, 1,
@Index-1)))

/*by kudychen 2011-9-28 */CREATE function [dbo].[SplitString](
@Input nvarchar(max), –input string to be separated @Separator
nvarchar(max)=’,’, –a string that delimit the substrings in the input
string @RemoveEmptyEntries bit=1 –the return value does not include
array elements that contain an empty string)returns @TABLE table (
[Id] int identity(1,1), [Value] nvarchar(max)) asbegin declare
@Index int, @Entry nvarchar(max) set @Index =
charindex(@Separator,@Input)

调用示例: 

if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry”) begin
insert into @TABLE([Value]) Values(@Entry) end

while (@Index0) begin set @Entry=ltrim(rtrim(substring(@Input, 1,
@Index-1))) if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and
@Entry”) begin insert into @TABLE([Value]) Values(@Entry) end

declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max) 
set @str1 = ‘1,2,3’ 
set @str2 = ‘1###2###3’ 
set @str3 = ‘1###2###3###’ 
select [Value] from [dbo].[SplitString](@str1, ‘,’, 1) 
select [Value] from [dbo].[SplitString](@str2, ‘###’, 1) 
select [Value] from [dbo].[SplitString](@str3, ‘###’, 0) 

set @Input = substring(@Input, @Index+datalength(@Separator)/2,
len(@Input)) set @Index = charindex(@Separator, @Input) end

set @Input = substring(@Input, @Index+datalength(@Separator)/2,
len(@Input)) set @Index = charindex(@Separator, @Input) end set
@Entry=ltrim(rtrim(@Input)) if (@RemoveEmptyEntries=0) or
(@RemoveEmptyEntries=1 and @Entry”) begin insert into @TABLE([Value])
Values(@Entry) end

 

set @Entry=ltrim(rtrim(@Input)) if (@RemoveEmptyEntries=0) or
(@RemoveEmptyEntries=1 and @Entry”) begin insert into @TABLE([Value])
Values(@Entry) end return

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图