Split String in SQL

Using a Table of Numbers:
This is the fastest way to unpack a comma-separated list of numbers in pure T-SQL. The trick is to use an auxiliary table of numbers: a one-column table with numbers from 1 and up. Here is how you can create a table with numbers from 1 to 999 999:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0

You can leverage a Number table to do the string parsing lightning fast:
create function [dbo].[Split] (@list nvarchar(max), @delimiter nchar(1) = N’,’)
returns table
as
return (
select
Number = row_number() over (order by Number)
, [Value] = ltrim(rtrim(convert(nvarchar(4000),
substring(@list, Number
, charindex(@delimiter, @list+@delimiter, Number)-Number
)
)))
from dbo.Numbers
where Number <= convert(int, len(@list))
and substring(@delimiter + @list, Number, 1) = @delimiter
)

You could try something like:
SELECT [Value] FROM dbo.ParseArray(‘Hello mrs Smith’,’ ‘) Where number = 2

Source: http://www.sommarskog.se/arrays-in-sql-2005.html