How do I split a string so I can access item x?

Question

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"?

Answer 1

You can use the PARSENAME function as the SQL server doesn’t have the split function. So apart from UDF, you can use this.

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)

PARSENAME: It takes the string as the first argument and splits it on the period character. It also takes the number as its 2nd argument, and the number specifies which segment of the string to be returned (It works from back to front).