CREATE FUNCTION ufn_ListToTbl (@vchlist varchar(8000))
RETURNS @tblValues TABLE (alpha varchar (40) ) AS

BEGIN
DECLARE @intix int,
@intpos int,
@vchstr varchar(8000),
@intnum int
SET @intpos = 1
SET @intix = 1
WHILE @intix > 0
BEGIN
SET @intix = charindex(',', @vchlist, @intpos)
IF @intix > 0
SET @vchstr = substring(@vchlist, @intpos, @intix - @intpos)
ELSE
SET @vchstr = substring(@vchlist, @intpos, len(@vchlist))
SET @vchstr = ltrim(rtrim(@vchstr))

INSERT @tblValues (alpha) VALUES(@vchstr)

SET @intpos = @intix + 1
END
RETURN
END



Roji. P. Thomas
SQL Server Programmer
--------------------------------------
----- Original Message -----
From: "Regis Biassala" <Regis.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Monday, January 19, 2004 8:13 PM
Subject: Comma delimitated string function


> Hi,
>
>
>
> Does one have a function that takes comma delimitated string and return
each
> string?
>
> Example:
>
> @p_string = 'ABD, HYTN, OPL, KUYT, NGH'
>
>
>
> The function should able to return it as follow:
>
> Select my_func('ABD, HYTN, OPL, KUYT, NGH')
>
> ---------------
>
> ABD
>
> HYTN
>
> OPL
>
> KUYT
>
> NGH
>
>
>
>
>
> Thanks,
>
> Regsi
>
>
>
> *********************************************************************
> This electronic transmission is strictly confidential and intended solely
> for the addressee. It may contain information which is covered by legal,
> professional or other privilege. If you are not the intended addressee,
> you must not disclose, copy or take any action in reliance of this
> transmission. If you have received this transmission in error,
> please notify the sender as soon as possible.
>
> This footnote also confirms that this message has been swept
> for computer viruses.
> **********************************************************************
>
>
MS Sql Server LazyDBA home page