T-SQL string function

SQL Server has a string function String_Split() but it is only available for versions above 2014 and it is not that flexible either. Moreover, the function parsename() only handles four part string where the string must be delimited by '.' While parsing a string users often want to get parts of a string  and often times need to write complex code using combination of substring() and charindex() function to get to individual parts of a string. Below you find a function in addition to existing function:

mssql_split_part (string text, delimiter text, field int)

That will split the string based on the delimiter and return the value on the field. For example:

mssql_split_part (‘this is a string’, ‘ ‘, 4) will return the value ‘string’ because it is the 4^th field in the string.

mssql_split_part (‘this;is;a;string’, ‘;‘, 1) will return ‘this’

Here is the sql code:

if OBJECT_ID('mssql_split_part') is  not null  drop function  mssql_split_part
go
create function  mssql_split_part(@v1 varchar(max), @v2 varchar(max), @v3 int)
returns varchar(max)
as
begin
declare @t1 table (c1 int identity,
             c2 varchar(max))
declare @a varchar(max), @b varchar(max)
while len(@v1) > 0 
begin
if charindex(@v2,@v1) = 0  --last value of string
begin
set @a = substring(@v1,1,len(@v1))
insert @t1 (c2) select @a
break
end
set @a = substring(@v1,1,charindex(@v2,@v1)-1)
set @v1 = stuff(@v1,1,charindex(@v2,@v1),NULL)
insert @t1 (c2) values (@a)
end
set @b = (select c2 from @t1 where c1 = @v3)
return @b
end
go