December 26, 2008

Parse a comma-delimited string


This T-SQL script parses a comma-delimited string and inserts  the values into a table.  It is a good function candidate too!  
DECLARE @MyString varchar (100), @Delimiter char (1), @NewValuevarchar(30), @Pos int, @NextPos int, @Last char(1)
SET @MyString = 'ca,fl,md,'
SET @Delimiter = ','
SET @NewValue = ''
SET @Last = RIGHT(@MyString, 1)
-- create temp table to hold split values
CREATE TABLE #myTable([CurrValue] varchar(100))
  -- process trailing comma
IF @Last <> @Delimiter
 BEGIN
 SET @MyString = @MyString + @Delimiter
END
  -- get position of first comma
SET @Pos = CHARINDEX(@Delimiter, @MyString)
SET @NextPos = 1
  -- split on comma & insert into table
WHILE (@pos <> 0)
BEGIN
SET @NewValue = SUBSTRING(@MyString, 1, @Pos - 1) INSERT
INTO #myTable (
[CurrValue]
) VALUES(
@NewValue
)
SET @MyString = SUBSTRING(@MyString, @pos + 1, LEN(@MyString))
SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter, @MyString)
END
  -- display results
SELECT * FROM #MyTable
  -- cleanup
DROP TABLE #MyTable



December 22, 2008

convert columnar result set to comma-delimited string


T-SQL for comma-delimited string:
DECLARE @RepRegion Varchar(500)
SELECT @RepRegion = COALESCE(@RepRegion + ',' , '') + [RepState]
FROM [dbo].[Reps]
WHERE [TerritoryID] = '12345'
AND [RepState] IN (SELECT DISTINCT [RepState]                             
FROM [dbo].[Reps]                             
WHERE [TerritoryID] = '12345' )
PRINT @RepRegion