Simply copy and paste the Split() function code in your T-SQL code editor, to select appropriate DB from database combo box and then press F5 key to create user defined Split() function.
Now you can use it as follows:
1. The first parameter of function is string that you need to split.
2. And second parameter of function is delimiter.
SELECT * FROM dbo.Split ('Token1;Token2;Token3;Token4;Token5',';')
The Expected output will be as follow:
Token1
Token2
Token3
Token4
Token5
CREATE FUNCTION dbo.Split
(
@String VARCHAR(8000),
@Delimiter NVARCHAR(1)
)
RETURNS @Tokens table (Token NVARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END 
Splitting strings based on patterns supported by LIKE and PATINDEX can be an interesting way to address a wide variety of problems.
2014-09-12 (first published: 2012-11-29)
32,079 reads
This article from Todd Fifield shows a Way to avoid the dreaded LIKE operator in your queries and dramatically speed up keyword searches.
2012-09-07 (first published: 2011-02-28)
35,449 reads
String searching and manipulation in SQL Server can be error-prone and tedious...unless you're armed with the techniques described in Robyn's string manipulation workbench, here newly revised with extra material from collaborator Phil Factor
2010-10-19
4,427 reads
How to change your "UPPER" or "lower" case strings to a mixed case
2010-10-18
6,081 reads
2010-07-20 (first published: 2010-06-23)
2,574 reads