root/branches/Abonament/SQL/Functions/SplitAsScalar.txt @ 754

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--================================================
--  Create Inline Table-valued Function template
--================================================

ALTER FUNCTION [dbo].[SplitAsScalar]
(@in_del_field      VARCHAR(4000),
 @in_pos            INT,
 @in_del            VARCHAR(4000) = '/')
RETURNS VARCHAR(4000)
AS
BEGIN
 /**
 * <hr>
 * SPLIT Scalar User-Defined function JG 1/13/2006
 * <hr>
 * The split function will take a delimted string value
 * and return a value from a location in the string.
 * <PRE>
 * FOR EXAMPLE...
 *
 * print dbo.split('franklin,delano,roosevelt',1,',')
 * print dbo.split('franklin,delano,roosevelt',2,',')
 * print dbo.split('franklin,delano,roosevelt',3,',')
 * GO
 *
 * THIS RETURNS...
 *
 * franklin
 * delano
 * roosevelt
 *
 * </PRE>
 * @param @in_del_field      Delimited Text string passed to the function
 * @param @in_pos            Position of value to return
 * @param @in_del            Delimiter.  DEFAULT value is ","
 * @headcom
 */
  DECLARE @retVal       VARCHAR(4000)
  DECLARE @varFldDel    VARCHAR(4000) SET @varFldDel = @in_del_field + @in_del
  DECLARE @i            int SET @i = 0
  DECLARE @varExit      VARCHAR(5) SET @varExit = 'FALSE'
  DECLARE @intLastLen   int SET @intLastLen = 1
 --
  IF @in_pos < 1
    SET @retVal = 'Not Valid Option, 1 is the first occurance of an item'
  ElSE
   --> loop through till the correct column is found
    WHILE @varExit = 'FALSE'
      BEGIN
        SET @i = @i + 1
        If @i > 1
          BEGIN
            SET @varFldDel = SUBSTRING(@varFldDel,@intLastLen,8000)
          END
        If CHARINDEX(@in_del,@varFldDel) < 1
        BEGIN
          RETURN NULL
        END
       --
        SET @retVal = SUBSTRING(@varFldDel,1,(CHARINDEX(@in_del,@varFldDel)-1))
        SET @intLastLen = LEN(@retVal) + 2
        If @i = @in_pos
          BREAK
      END
 --
  RETURN @retVal
 --
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO