摘要:MSSQL_Split功能函數
USE [PublicDBA]
GO
/****** Object: UserDefinedFunction [dbo].[func_Split] Script Date: 01/02/2012 14:15:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================
-- Author:
-- Create date:
-- Description: [MS-SQL] Split 功能。( 將字串分解成Table欄位Rows )
-- ===============================================================
/* Funtion「呼叫範例」
-----------------------------------------------------------------------------
Select *
From Split(',' , '謝,謝,你,9,5,2,7,!')
--- OR ----------------------------------------------------
Select *
From Split('$' , '謝$謝$你$9$5$2$7$!')
-----------------------------------------------------------------------------
*/
ALTER FUNCTION [dbo].[func_Split]
(
@Separator nvarchar(10), -- 分隔符號
@StringToSplit nvarchar(max) -- 字串集 (如上「呼叫範例」)
)
RETURNS @tTable table ([Row_Index] int, [String] nvarchar(max))
BEGIN
DECLARE @Count int
--DECLARE @tTable table(ROW_INDEX int, String nvarchar(max))
DECLARE @NextString nvarchar(max)
DECLARE @SplitPosition int
DECLARE @NextPosition int
DECLARE @SplitCheck nvarchar(1)
Set @Count = 0;
Set @NextString = ''
Set @SplitCheck = Right(@StringToSplit,Len(@Separator))
-- 如果字串最後不是分隔符號，則一直執行迴圈
IF (@SplitCheck <> @Separator )
Begin
Set @StringToSplit = @StringToSplit + @Separator
End
-- 取得第一個分隔符號位置
Set @SplitPosition = CharIndex(@Separator,@StringToSplit)
Set @NextPosition = 1
-- 取得其他分隔符號位置，並Insert到Table中
WHILE (@SplitPosition <> 0)
Begin
Set @Count = @Count + 1
Set @NextString = SubString(@StringToSplit,1,@SplitPosition-1)
Insert Into @tTable ([ROW_INDEX],[String]) Values (@Count,@NextString)
Set @StringToSplit = SubString(@StringToSplit,@SplitPosition + Len(@Separator),Len(@StringToSplit))
Set @NextPosition = @SplitPosition
Set @SplitPosition = CharIndex(@Separator,@StringToSplit)
End
RETURN
END