在SQL Server的字符串函数中,没有类似于C#的split功能的函数,下面这个函数实现了这个功能:
代码
USE
[
database_name
]
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 阿瑞
-- Create date: 2008-03-19
-- Description: split函数
-- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
-- =============================================
ALTER 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
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 阿瑞
-- Create date: 2008-03-19
-- Description: split函数
-- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
-- =============================================
ALTER 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',':')
结果: