Mssql split 함수로 구현

대체 왜 이런 함수가 기본으로 없는거요?

 

/* ------------------------------------------------------------

* Split 함수

*  SELECT dbo.fn_split( '123-4567' , 1 , '-' )

------------------------------------------------------------ */

​

CREATE FUNCTION [dbo].[fn_split] (

  @str varchar(1000),        

  @spliter varchar(5),

  @idx int

)
RETURNS varchar(1000)
AS
BEGIN

  DECLARE @NextString NVARCHAR(40)
  DECLARE @Pos INT
  DECLARE @NextPos INT
  DECLARE @String NVARCHAR(40)
  DECLARE @Delimiter NVARCHAR(40)
  DECLARE @Loop INT

 

  SET @String =@str
  SET @Delimiter = @spliter
  SET @String = @String + @Delimiter
  SET @Pos = charindex(@Delimiter,@String)
  SET @Loop = 0

  SET @NextString =''

  WHILE (@pos <> 0)
    BEGIN
    SET @NextString = substring(@String,1,@Pos - 1)
    if (@Loop =@idx) break;

    SET @String = substring(@String,@pos+1,len(@String))
    SET @pos = charindex(@Delimiter,@String)
    SET @Loop = @Loop + 1
  END

  RETURN @NextString

END