to parse a cvs string and return a table with “Val” as the column name.
=====
ALTER FUNCTION [dbo].[ParseCSV] (
@param varchar(8000),
@Delimeter varchar(10)
)
RETURNS @tblRes TABLE
(
-- Columns returned by the function
Val varchar(100)
)
AS begin
DECLARE @Val varchar(100)
DECLARE @StartPos int, @Length int
WHILE LEN(@param) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @param)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@param) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Val = SUBSTRING(@param, 1, @StartPos - 1)
SET @param = SUBSTRING(@param, @StartPos + 1, LEN(@param) - @StartPos)
END
ELSE
BEGIN
set @param = ltrim(rtrim(@param))
SET @Val = @param
SET @param = ''
END
set @Val = Ltrim(Rtrim(@Val))
INSERT @tblRes (Val) VALUES(@Val)
END
--Show all records in the @tblRes table
--SELECT * FROM @tblRes
RETURN
end