SPLIT_PART splits a string on the specified delimiter and returns the substring at the specified column name. While SPLIT_PART ships with Amazon Redshift, Microsoft's SQL Server only has STRING_SPLIT, which splits a string into rows of substrings, based on a specified delimiter.

Below is an implementation of SPLIT_PART for SQL Server.

Syntax:

SPLIT_PART(delimiter, string, substring_index)

Note: The substring_index begins at 1, not 0

Example:

select listtime, split_part('-',listtime,1) as year,
split_part('-',listtime,2) as month, 
split_part(split_part('-',listtime,3),' ',1) as date 
from listing limit 5;

      listtime       | year | month | date
---------------------+------+-------+------
 2008-03-05 12:25:29 | 2008 | 03    | 05
 2008-09-09 08:03:36 | 2008 | 09    | 09
 2008-09-26 05:43:12 | 2008 | 09    | 26
 2008-10-04 02:00:30 | 2008 | 10    | 04
 2008-01-06 08:33:11 | 2008 | 01    | 06

Function code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FN_SPLIT_PART] (
@SearchedStr varchar(1),
@TargetStr varchar(2000),  
@Occurrence int
)

RETURNS varchar(1024)
as
begin
	declare @currPos int, @pos int, @counter int, @ret varchar(1000), @TargetStrLen int, @TotalOccurences  int
	set @TargetStrLen  = LEN (@TargetStr)
	set @TotalOccurences = DATALENGTH(@TargetStr)-DATALENGTH(REPLACE(@TargetStr,@SearchedStr,''))
	if @TargetStrLen = 0
		RETURN(@ret)
	else
	begin
		set @pos = CHARINDEX(@SearchedStr, @TargetStr,  1)
		if @pos = 0
			RETURN(@ret)
		else
		begin
			set @counter = 1		
			if @Occurrence = 1 
				set @ret = LEFT(@TargetStr, @pos -1) 
			else
			begin
				while (@counter < @Occurrence)
				begin
					set @currPos = CHARINDEX(@SearchedStr, @TargetStr, @pos + 1)
					set @counter = @counter + 1
					if @currPos =0
						set @ret= SUBSTRING (@TargetStr, @pos +1, @TargetStrLen) 
					else
						set @ret = SUBSTRING(@TargetStr, @pos+1, @currPos-@pos-1)
					set @pos = @currPos	
				end
					if (@counter > @TotalOccurences+1)
					set @ret = ''
			end
		end
	end
		RETURN(@ret)
end