Home > Article > Database > sql 切割字符串Split（）函数

sql 切割字符串Split（）函数

WBOYOriginal: 2016-06-07 17:48:436830browse

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

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

代码如下

复制代码

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

如何使用：

代码如下

复制代码

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###'

[Value] from [dbo].[SplitString](@str1, ',', 1)
select [Value] from [dbo].[SplitString](@str2, '###', 1)
select [Value] from [dbo].[SplitString](@str3, '###', 0)

里面还有个自增的[Id]字段哦，在某些情况下有可能会用上的，例如根据Id来保存排序等等。

例如根据某表的ID保存排序：

代码如下	复制代码
update a set a.[Order]=t.[Id] from [dbo].[表] as a join [dbo].SplitString('1,2,3', ',', 1) as t on a.[Id]=t.[Value]

实例

代码如下

复制代码

create function [dbo].[split]
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
    declare @i int
    set @SourceSql = rtrim(ltrim(@SourceSql))
    set @i = charindex(@StrSeprate,@SourceSql)
    while @i >= 1
    begin
        if len(left(@SourceSql,@i-1))>0
        begin
            insert @temp values(left(@SourceSql,@i-1))
        end
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql ''
        insert @temp values(@SourceSql)
    return
end

select * from split(',,777,,11,,888,88,,1122,888,88,77,,00,,00',',')

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：sql server2005还原数据库的方法（图文说明）Next article：mssqlserver数据库自动备份四种方法

See more

sql 切割字符串Split（）函数

Related articles