SQL Server Performance

Comma Seperated values to table form

Discussion in 'Contribute Your SQL Server Scripts' started by veereshnashi, Feb 23, 2006.

  1. veereshnashi New Member

    Many times Programmers pass comma separated values from Front end to Procedures and write code in the backend to get values from comma seperated. <br />This script generates the table from Comma_seperated values. Hope this help for the programmers. I as a DBA Provided this script to my developers.<br />Here is the Script.<br /><br /><br /><br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS OFF <br />GO<br />---************************************************************************************************<br />-- AUTHOR : VEERESH V NASHI (DBA)<br />-- CREATED ON : 06-01-2006<br />-- PUROSE : GENERAL PUPROSE FUNCTION USEFULL WHEN DEVELOPER NEEDS TO <br />-- BREAK THE COMMA SEPARATED VALUES INTO TEMPORARY TABLE :<br />----USAGE<br />-- -- SELECT * FROM dbo.udf_UTL_Tockenize_Table( 'VEERESH , YUVARJA,HARI, SUJITH , MAHESH ,D')<br /><br />-- OUTPUT <br /><br />-- Tocken_ID Tocken_Value <br />-- --------- ---------------<br />-- 1 VEERESH<br />-- 2 YUVARJA<br />-- 3 HARI<br />-- 4 SUJITH<br />-- 5 MAHESH<br />-- 6 D<br /><br />-- -- SELECT * FROM @Tocken_Table<br />---************************************************************************************************<br /><br /><br />CREATE FUNCTION dbo.udf_UTL_Tockenize_Table(<br />@Comma_Value VARCHAR(8000) <br />) RETURNS @Tocken_Table TABLE(Tocken_ID INT IDENTITY,<br /> Tocken_Value VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> )<br />AS<br /><br />BEGIN<br /><br />DECLARE @Counter INT <br />DECLARE @Comma_Point INT <br />DECLARE @String_Length INT<br />DECLARE @String_Value VARCHAR(75)<br /><br />SET @Counter = 1 <br />SET @Comma_Point = 0<br />SET @String_Length = 0 <br />SET @Comma_Value = RTRIM(LTRIM(@Comma_Value)) <br />SET @String_Length = LEN(@Comma_Value)<br /><br />IF @String_Length &lt;&gt; 0 <br />BEGIN <br />SET @Comma_Value = @Comma_Value +','<br />SET @Comma_Point = CHARINDEX (',',@Comma_Value,1)<br />WHILE @Comma_Point &lt;&gt; 0<br />BEGIN<br />SET @String_Value = RTRIM(LTRIM( LEFT(@Comma_Value,@Comma_Point-1))) <br />IF LEN(@String_Value) &lt;&gt; 0 <br />INSERT @Tocken_Table VALUES (@String_Value)<br />SET @Comma_Value = SUBSTRING(@Comma_Value,@Comma_Point+1,@String_Length)<br />SET @Comma_Point = CHARINDEX (',',@Comma_Value,1)<br />END <br />END<br /><br />RETURN <br />END<br /><br /><br />GO<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br /><br /><br /><br />You Store, I Manage
  2. Madhivanan Moderator

Share This Page