Category Archives: Scripts

How to Split comma or any other character delimited string into a Table in Sql Server

Scripts, Sql Server, , , , , , , ,

Many a time we come across a scenario where we pass a comma or any other character delimited string to stored procedure and in stored procedure we want to fetch data from other tables based this delimited string. We can’t use this delimited string directly in the in clause as Sql treats it as one string. So, the solution for this is to get a table by splitting the delimited string by the delimiter character and then join this resultant table data with other table columns.

In Sql Server we have mechanism where we can take table as a input parameter from the application code. So if you like to pass a table from your code with multiple rows instead of passing a delimited string to stored procedure and splitting it in SP, then you may like to read the article Table-Valued Parameters in Sql Server. But I would prefer passing the comma delimited string to stored procedure and split it in the SP.

In Sql Server we have multiple approaches to achieve this. This article lists couple of them. Please let me know which one you use and if you use some-other approach let me know, so that we can help the Sql Server developer community

APPROACH 1: Sql Server 2016 STRING_SPLIT function

STRING_SPLIT is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.

Below example shows how we can use STRING_SPLIT function to splits the comma separated string.

SELECT * 
FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')

RESULT:
Sql STRING_SPLIT Function Example 1

To know in detail about the STRING_SPLIT function you may like to read the article: STRING_SPLIT function in Sql Server 2016

APPROACH 2: Using While Loop and Sql String Functions

We can crate a table valued function like the below which is using WHILE loop and Sql String functions like CHARINDEX and SUBSTRING to split the string. This should work in all the versions of Sql Server.

CREATE FUNCTION [dbo].StringSplit
(
	@String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN      
 DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),
		@Value VARCHAR(MAX), @StartPosition INT = 1

 IF @SeparatorPosition = 0	
  BEGIN	
   INSERT INTO @RESULT VALUES(@String)
   RETURN
  END
	
 SET @String = @String + @Separator
 WHILE @SeparatorPosition > 0
  BEGIN
   SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)

   IF( @Value <> ''  ) 
    INSERT INTO @RESULT VALUES(@Value)
  
   SET @StartPosition = @SeparatorPosition + 1
   SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)
  END     
	
 RETURN
END

Below example shows how we can use the above function to split the comma delimited string

SELECT * 
FROM StringSplit('Basavaraj,Kalpana,Shree',',')

RESULT:
StringSplit

APPROACH 3: Using XML

We can crate a table valued function like the below which is using Sql XML feature to split the string.

CREATE FUNCTION [dbo].StringSplitXML
(
    @String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN     
 DECLARE @XML XML
 SET @XML = CAST(
    ('<i>' + REPLACE(@String, @Separator, '</i><i>') + '</i>')
    AS XML)

 INSERT INTO @RESULT
 SELECT t.i.value('.', 'VARCHAR(MAX)') 
 FROM @XML.nodes('i') AS t(i)
 WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''

 RETURN
END

Below example shows how we can use the above function to split the comma delimited string

SELECT * 
FROM StringSplitXML('Basavaraj,Kalpana,Shree',',')

RESULT:
StringSplitXML

How to get all the Tables with or without an Identity column in Sql Server?

Scripts, Sql Server, , , , , , , ,

This article provides the script to find all the Tables with or without an Identity column

Tables with Identity column

We can write a query like below to get all the Tables with Identity column:

SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
      AND type = 'U'

Tables without Identity column

We can write a query like below to get all the Tables without Identity column:

SELECT name 'Table without Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
      AND type = 'U'

EXAMPLE

Let us understand the above scripts with an example. To understand this, let us create a demo database SqlHintsDemoDB with Customers table having an identity column and an Orders table without identity column.

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Identity column.*/
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY (1, 1) 
              PRIMARY KEY CLUSTERED NOT NULL ,
    FirstName Varchar(50),
    LastName Varchar(50))
GO
/*Let us create Orders Table without and Identity column.*/
CREATE TABLE dbo.Orders (
    OrderId int NOT NULL ,
    CustomerId int NOT NULL ,
    CreationDT DATETIME NOT NULL)
GO

Now let us run the queries to get the list of all Tables with or without identity column and verify the result:

Sql List of Tables with or without Identity column

How to get Yearly data in Sql Server

Scripts, Sql Server, , , , , , , , , ,

This article demonstrate how to get yearly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different yearly aggregated sales data formats.

Yearly data in Sql Server

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in the past 5 years using the below script.

CREATE DATABASE SqlHintsYearlyData
GO
USE SqlHintsYearlyData
GO
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0 to 5 years as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(YEAR, - ROUND(5 * RAND(), 0),GETDATE()))
GO 1000

Demo 1: Getting Yearly Data by using Group By

SELECT YEAR(SalesDate) [Year], Count(1) [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)
ORDER BY 1

RESULT:
Yearly data in Sql Server by Group By

Demo 2: Getting Yearly data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server

SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
      Count(1)  [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)) AS YearlySalesData
PIVOT( SUM([Sales Count])   
    FOR [Year] IN ([2010],[2011],[2012],
 [2013],[2014],[2015])) AS YearPivot

RESULT:
Yearly data in Sql Server by Pivot

Demo 3: Getting Yearly data using Dynamic PIVOT

[ALSO READ] Dynamic PIVOT in Sql Server

Problem with Demo 2 approach is , it is using the static PIVOT to get the data. Which requires the developer to specify all the years as the pivot columns manually. We can re-write the Demo 2 examples by using Dynamic pivot approach as below which doesn’t require the developer to manually specify the years column.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT YEAR(SalesDate) [Year],  Count(1)  [Sales Count]   
	INTO #PivotSalesData
FROM #Sales
GROUP BY YEAR(SalesDate) 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Year])
FROM (SELECT DISTINCT [Year] FROM #PivotSalesData) AS Years
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + '
    FROM #PivotSalesData
    PIVOT(SUM( [Sales Count]   ) 
          FOR [Year] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Yearly data in Sql Server by Pivot

[ALSO READ]:
How to get Quarterly Data in Sql Server
How to get Monthly Data in Sql Server
How to get Daily data in Sql Server
How to get Hourly data in Sql Server