Split string in SQL - Pick the first substring

[HardDisk]

Using SQL Server 2005, how do I split a string so I can access first sub string?

For example, take the string "This is a Cat". How can I split the string by a space and access the item at index 1 which should return "This"?
May be we can split in an array ?
I am thinking in C# mode

[Chand]

I don't believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to use the PARSENAME function:

SELECT PARSENAME(REPLACE('This is a cat', ' ', '.'), 2)

PARSENAME takes a string and splits it on the period character. It takes a number as it's second argument, and that number specifies which segment of the string to return (working from back to front).

SELECT PARSENAME(REPLACE('This is a cat', ' ', '.'), 3) --return Hello

But there is a limitation. It won't work if you have period (.) in string

[Chand]

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).
http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

It is a long read but I am sure you will find it helpful.