T-SQL : Comma separated values to table

In this post, I'm trying to explain you how one parse the comma separated string to a table so that which can be used as any other regular table.

This would work only SQL Server 2005 Onwards releases as I'm creating table valued function to accomplish this.

I have created a function called [dbo].[GetDelimitedStringToTable] which returns the table having two columns ( 1 for Index and 1 for value).

Please find the definition for the function [dbo].[GetDelimitedStringToTable] 

/**********************************************************************

     

Object Name : GetDelimitedStringToTable

Purpose     : Splits the given input delimited string and returns a table.

              Returns 2 columns

                  ID - Represents the IDENTITY column.     

                  Value - Represents the data of a delimited string.

Author      : Karthik D V

Created Date: 06-Jun-2011

Sample Calls:

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( '1,2,3,4', DEFAULT )

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The Doc#Prasob - Super Singer', '#' )

                       

Modification History:

----------------------------------------------------------------------

Date        Modified By       Modification Details

----------------------------------------------------------------------

----------------------------------------------------------------------

   

**********************************************************************/

CREATE FUNCTION [dbo].[GetDelimitedStringToTable]

(

      @DelimitedString  VARCHAR(8000) ,

      @Delimiter        CHAR(1) = ','

)

RETURNS @ParsedTable TABLE(ID INT IDENTITY(0,1), Value VARCHAR(MAX))

BEGIN

      ;WITH CTE ( StartPos, EndPos ) AS

            (

                  SELECT 1, CHARINDEX(@Delimiter, @DelimitedString)

                  UNION ALL

                  SELECT

                         EndPos + (LEN(@Delimiter)), 

                         CHARINDEX(@Delimiter, @DelimitedString, EndPos

                         + (DATALENGTH(@Delimiter)))

                  FROM CTE

                  WHERE EndPos > 0

            )

            INSERT INTO @ParsedTable ( Value)

            SELECT

                  Value = SUBSTRING ( @DelimitedString, StartPos, CASE WHEN EndPos > 0 THEN EndPos - StartPos ELSE 4000 END )

            FROM CTE

            OPTION (MAXRECURSION 32767)

           

            RETURN

END

GO

Now, lets look at some samples:

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The Doc#Prasob - Super Singer', '#' )

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( '1,2,3,4', DEFAULT )

T-SQL : Column to Comma separated string

We often get into the problem of getting a particular column value as a comma separated string in SQL. I don’t know how many of you have faced this problem, but in my case, it is many a times. Hence I decided to blog here !!! :)

Let’s say I have the following Query with the given result:

SELECT AlarmTypeID FROM AlarmType

Now, we need to get the comma separated value for the column AlarmTypeID.

This can be done with the below mentioned query:

SELECT AlarmTypeID = STUFF((SELECT ',' + CONVERT(VARCHAR,AlarmTypeID) 

FROM AlarmType FOR XML PATH('')), 1,1, ' ')

When you run the above query, you would get the result like shown below:

SQL- Identify table dependencies

Hi!,

Many a times we get into the problem of identifying the dependency of the given table, normally what we do is open SQL Server Object Explorer, select database, select a particular table and then right click on it and then select View dependencies option. This works fine when we deal with individual table.

But what If I need to know the dependency of multiple tables? Obviously we can’t go and do the above said steps to all the tables.

So, to get rid of this I prepared the SQL query which lists all tables available in a database along with dependency level. You can tailor this  to filter for a particular list of tables.

The DependencyLevel column contains ZERO, then those tables are independent.

Here is the query:

WITH fk_tables AS

(

    SELECT

            from_schema = s1.name

            ,from_table = o1.Name

            ,to_schema  = s2.name

            ,to_table   = o2.Name

    FROM

            sys.foreign_keys fk   

            INNER JOIN sys.objects o1 ON ( fk.parent_object_id = o1.object_id )  

            INNER JOIN sys.schemas s1 ON ( o1.schema_id = s1.schema_id )   

            INNER JOIN sys.objects o2 ON ( fk.referenced_object_id = o2.object_id )   

            INNER JOIN sys.schemas s2 ON ( o2.schema_id = s2.schema_id )

    /*For the purposes of finding dependency hierarchy      

        we're not worried about self-referencing tables*/

    WHERE

            NOT( s1.name = s2.name AND o1.name = o2.name)

)

,

ordered_tables ( schemaName, tableName, DependencyLevel )

 AS

(        SELECT   

                  s.name

                  ,t.name

                  ,0   

        FROM   

                  ( SELECT * FROM sys.tables WHERE name <> 'sysdiagrams') t   

                  INNER JOIN sys.schemas s ON ( t.schema_id = s.schema_id )

                  LEFT OUTER JOIN fk_tables fk ON ( s.name = fk.from_schema AND t.name = fk.from_table )   

        WHERE

                  fk.from_schema IS NULL

                 

        UNION ALL

       

        SELECT   

                  fk.from_schema

                  ,fk.from_table

                  ,ot.DependencyLevel + 1   

        FROM

                  fk_tables fk   

                  INNER JOIN ordered_tables ot ON ( fk.to_schema = ot.schemaName AND fk.to_table = ot.tableName )

)

SELECT DISTINCT

      TableName = '[' + ot.schemaName + '].[' +  ot.tableName + ']'

      , ot.DependencyLevel

FROM

      ordered_tables ot

      INNER JOIN (

                              SELECT

                                    schemaName

                                    ,tableName

                                    ,MAX(DependencyLevel) maxLevel       

                              FROM

                                    ordered_tables       

                              GROUP BY

                                    schemaName,tableName

                        ) mx ON ( ot.schemaName = mx.schemaName

                                      AND ot.tableName = mx.tableName

                                      AND mx.maxLevel = ot.DependencyLevel )

WHERE

      ot.schemaName = 'dbo'

ORDER BY

       ot.DependencyLevel ASC

String.Format in T-SQL

I have been searching for the string.format equivalent functionality in sql server.

Unfortunately I could not find exact match though I got xp_sprintf.

xp_sprintf will do format only when you add your message to system messages via using sp_addmessage procedure.

So, I came up with a function which works almost very similar to the way we do in C#.

For Example : In C# we format message like this.

string msg = string.format("Hi{0}, Welcome to our site {1}. Thank you {0}", "Karthik D V", "Google Mania");

Now, in T-sql, with my new function you can write same thing like this! :

DECLARE @msg NVARCHAR(4000)

SET @msg = 'Hi {0}, Welcome to our site {1}. Thank you {0}'

SELECT @msg = dbo.FormatString(@msg, N'Karthik D V, Google Mania')

PRINT @msg

OUTPUT :

Hi Karthik D V, Welcome to our site Google Mania. Thank you Karthik D V

Source Code:

-------------------------------------------------------------------------------------------------------------

IF OBJECT_ID( N'[dbo].[FormatString]', 'FN' ) IS NOT NULL

DROP FUNCTION [dbo].[FormatString]

GO

/***************************************************

Object Name : FormatString

Purpose : Returns the formatted string.

Author : Karthik D V

Created Date: 21-Dec-2010

Sample Call:

SELECT dbo.FormatString ( N'Format {0} {1} {2} {0}', N'1,2,3' )

Modification History:

----------------------------------------------------------------------------

Date Modified By Modification Details

----------------------------------------------------------------------------

----------------------------------------------------------------------------

*******************************************/

CREATE FUNCTION [dbo].[FormatString]

(

@Format NVARCHAR(4000) ,

@Parameters NVARCHAR(4000)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE @Message NVARCHAR(400),

@Delimiter CHAR(1)

DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), ParamterVARCHAR(1000) )

SELECT @Message = @Format, @Delimiter = ','

;WITH CTE (StartPos, EndPos) AS

(

SELECT 1, CHARINDEX(@Delimiter, @Parameters)

UNION ALL

SELECT EndPos + (LEN(@Delimiter)), CHARINDEX(@Delimiter,@Parameters, EndPos + (LEN(@Delimiter)))

FROM CTE

WHERE EndPos > 0

)

INSERT INTO @ParamTable ( Paramter )

SELECT

[ID] = SUBSTRING ( @Parameters, StartPos, CASE WHENEndPos > 0 THEN EndPos - StartPos ELSE 4000 END )

FROM CTE

UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Paramter )

RETURN @Message

END

GO

GRANT EXECUTE,REFERENCES ON [dbo].[FormatString] TO [public]

GO