# fnSplits 字串分割(常用於傳陣列資料)
###### tags: `SQL`

## 語法 ( 資料表值函式 )

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[Splits]( @InStr nvarchar(MAX) , @s_char nvarchar(1) )
Returns @tb Table ( SNO int , Element nvarchar(100) )
As
Begin
    /*依據傳入字元進行字串分割，回傳Table*/
    /*
    Declare @InStr nvarchar(2000) , @s_char nvarchar(1);
    Set @InStr = '字串一,字串2,字串3,字串4';
    Set @s_char = ',';*/
  
    Set @InStr = @s_char + @InStr + @s_char;
    Declare @p1 Int , @p2 Int , @data nvarchar(100) , @sno int;
    Set @p1 = -1 ; Set @p2 = -1 ; Set @data = '' ; Set @sno = 0;
    While ( 0 Not In (@P1,@P2) ) Begin
  Set @p1 = CharIndex(@s_char,@InStr,@p1+1);
  Set @p2 = CharIndex(@s_char,@InStr,@p1+1);
  If ( 0 In (@p1,@p2) )  
      Break;
  Set @data = SubString(@InStr,@p1+1,@p2-@p1-1);
  if ( @data <> '' ) Begin
      Set @sno = @sno +1;
      Insert Into @tb ( SNO , Element ) Values ( @sno , @data )
  End
    End
    Return
End 

---

## 使用方法

Select * FROM dbo.Splits('AAA,BBB,CCC',',')

---

## 結果