Using SQL Server, how do I split a string so I can access item x?
Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
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 hijack the PARSENAME function:
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME takes a string and splits it on the period character. It takes a number as its second argument, and that number specifies which segment of the string to return (working from back to front).
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello
Obvious problem is when the string already contains a period. I still think using a UDF is the best way...any other suggestions?