I’m trying to figure out a way to select specific values from a String that is separated by a delimiter, in this case commas. I need to report each value after a comma in a separate field. I’ll include an example below for reference:
String: FirstVal,FirstName,HomeAddress,HomeCity,HomeCountry
This “FirstVal” value can be ignored, I don’t need to report that one, but I need to report the one after the first comma, and each individual one after each of the other commas, each in a separate field. Also, I don’t control length of the String or the length of each value, in other words there can be an infinite number of values with different lengths separated by commas. However, I do know the values can only be VARCHAR, so conversion/casting won’t be a problem. One last thing, I’m on SQL Server 2012, so the STRING_SPLIT Function unfortunately can’t be used, in case someone has a way of doing this by employing that Function.
Since the first value can be ignored, this is what I need to report from the String:
NameField: FirstName
AddressField: HomeAddress
AddressCity: HomeCity
AddressCountry: HomeCountry
I’ve been looking through the forum for a viable solution, and I’ve been experimenting with some iterations of CHARINDEX, but that doesn’t seem to be cutting it, so I’d really appreciate some assistance!
Thanks in advance everyone!
Here is one easy way to tackle this one. It is using the DelimitedSplit8K function from Jeff Moden which you can find here.
declare @String varchar(200) = 'FirstVal,FirstName,HomeAddress,HomeCity,HomeCountry'
select NameField = max(case when s.ItemNumber = 2 then s.Item end)
, AddressField = max(case when s.ItemNumber = 3 then s.Item end)
, AddressCity = max(case when s.ItemNumber = 4 then s.Item end)
, AddressCountry = max(case when s.ItemNumber = 5 then s.Item end)
from dbo.DelimitedSplit8K(@String, ',') s
Output:
NameField AddressField AddressCity AddressCountry
FirstName HomeAddress HomeCity HomeCountry