Menu
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"?
851
Split() Function : In MS Access, the Split() function splits the string into an array of strings. In this function, we will pass the string and the separator by which the function will separate the string. If we pass no separator then it will separate the string on the basis of the space.
The split() method splits a string into an array of substrings. The split() method returns the new array. The split() method does not change the original string. If (" ") is used as separator, the string is split between words.
To split a string with specific character as delimiter in Java, call split() method on the string object, and pass the specific character as argument to the split() method. The method returns a String Array with the splits as elements in the array.
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?
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
