程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL函數類似程序中split的方法，來分割某一個字段

SQL函數類似程序中split的方法，來分割某一個字段

編輯：關於SqlServer

類似於 aa/bb/cc/dd/ee/ff 得到 aa cc ee
aaa/bbb/ccc/ddd/eee/ff/ggg 得到 aaa ccc eee
split後取得斜槓之間的位置是固定的

以下是參考函數

1：==================================================================
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10))
insert   into   isc_catalog   values('1',   '0',   '中國')
insert   into   isc_catalog   values('2',   '1',   '湖南省')
insert   into   isc_catalog   values('3',   '1',   '廣東省')
insert   into   isc_catalog   values('4',   '3',   '廣州市')
insert   into   isc_catalog   values('5',   '2',   '長沙市')
insert   into   isc_catalog   values('6',   '4',   '白雲區')
insert   into   isc_catalog   values('7',   '5',   '雨花區')
insert   into   isc_catalog   values('8',   '2',   '婁底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
insert   into   isc_catalog   values('10',   '6',   '白雲小區A')--新增一條記錄

go
create function F_test(@nid int)
returns nvarchar(1000)
as
begin
    declare @s nvarchar(1000),@npid int
    select @s=rtrim(ccategory),@npid=npid from isc_catalog where nid=@nid
    if @s is null
        return null

    return isnull(dbo.F_test(@npid)+',','')+@s --用,分隔
    --@s+isnull('.'+dbo.F_test(@npid),'')
end
go

--加一個分隔函數:
create function F_split(
                @s varchar(8000),          --包含多個數據項的字符串
                @pos int,                 --要獲取的數據項的位置
                @split varchar(10)        --數據分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符長度
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))
END
GO

select
    dbo.F_split(dbo.F_test(nid),1,','),
    coalesce(dbo.F_split(dbo.F_test(nid),2,','),dbo.F_split(dbo.F_test(nid),1,',')),
    coalesce(dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,',')),
    coalesce(dbo.F_split(dbo.F_test(nid),4,','),dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,',')),
    coalesce(dbo.F_split(dbo.F_test(nid),5,','),dbo.F_split(dbo.F_test(nid),4,','),dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,','))
from
    isc_catalog a
where
    not exists(select 1 from isc_catalog where npid=a.nid)

/*
中國    湖南省    長沙市    雨花區    雨花區
中國    湖南省    婁底市    婁底市    婁底市
中國    北京市    北京市    北京市    北京市
中國    廣東省    廣州市    白雲區    白雲小區A
*/

--drop function F_split,F_test
--drop table isc_catalog

===================================================================

2：================================================================

create FUNCTION dbo.splitString
(
    @string VARCHAR(MAX),
    @delimiter CHAR(1),
    @rep int =1
)
RETURNS varchar(10)
BEGIN

    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    declare @ic int
    set @ic = 1
    WHILE @start < LEN(@string) + 1 BEGIN
        if(@ic = @rep) return substring(@string, @start, @end-@start)
        IF @end = 0
            SET @end = LEN(@string) + 1

        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
        set @ic = @ic + 1
    END

    return null
END

create table ta(col varchar(30))
insert into ta values('[ad][ba][daf]')
insert into ta values('[erf][afda][dasfasdf]')
insert into ta values('[fas][fase][reb]')

select dbo.SplitString(col, ']', 1)+']' as col1,
dbo.SplitString(col, ']', 2)+']' as col2,
dbo.SplitString(col, ']', 3)+']' as col3
from ta

col1                 col2                 col3
-----------   -----------   -----------
[ad]                 [ba]                 [daf]
[erf]               [afda]             [dasfasdf]
[fas]               [fase]             [reb]
====================================================================

3：=================================================================

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = N'Split' )
    DROP FUNCTION Split
GO

CREATE FUNCTION dbo.Split
(
@ItemList VARCHAR(4000),
@delimiter VARCHAR(10)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))

AS
    BEGIN

    WHILE CHARINDEX(@delimiter, @ItemList)>0
        BEGIN
            INSERT @IDTable SELECT LEFT(@ItemList,CHARINDEX(@delimiter,@ItemList)-1)
            SET @ItemList=STUFF(@ItemList,1,CHARINDEX(@delimiter,@ItemList),'')
        END
        INSERT @IDTable SELECT @ItemList
    RETURN

    END
GO

SELECT * FROM dbo.SPLIT('aaaa,bbb,ccc,eee',',')
GO
=====================================================================

4：==================================================================

create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return
end
go

--調用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split
=====================================================================

5：==================================================================

CREATE FUNCTION f_splitSTR(
@s     varchar(8000), --待分拆的字符串
@split varchar(10)     --數據分隔符
)RETURNS TABLE
AS
RETURN(
    SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
    FROM tb_splitSTR
    WHERE ID<=LEN(@s+'a')
        AND CHARINDEX(@split,@split+@s,ID)=ID)
GO