SQL Script to Split a String

I wondered to see the shortest way to split a string using T-SQL code. Usually we need to use a user defined function to split string but we can use one of the T-SQL function when the string contains 4 or less than 4 words.

Here is the SQL Script to split a string

Syntax: SELECT PARSENAME (REPLACE(String, Delimiter, ‘.’), Word_Number)

Example:

DECLARE @String VARCHAR(100)

SET @String =’SQL Server 2008 R2′

— Here we find 8 words with Delimiter as space (‘ ‘)

— Now Split the string

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 1)

Output:  R2

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 2)

Output:  2008

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 3)

Output:  Server

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 4)

Output:

SQL

Note: As we knows that the function Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name. So we can use it when the string contains words <=4.