Split Function in Sql Server to break Comma-Separated Strings into Table

Sql Server does not (on my knowledge) have in-build Split function.

Split function in general on all platforms would have comma-separated string value to be split into individual strings.

In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value (‘abc,cde,fgh’) into a temp table with each string as rows.

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

Split function can be Used as

select * from Split('Orange,Red,Yellow,White,Black,,',',')

Above Split function can be used to pass parameter to IN clause in sql server.

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates.

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Return Value = 0 Year

SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter

SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month

SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day

SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day

SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week

SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour

SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute

SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Return Value = 86400000 millisecond