自訂SQL的Split函數

 

-- =============================================
-- Author:  hy
-- Create date: 2008-12-03
-- 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