Split string returning a record

Question

Hi,

I have a single column table which looks like this:

Gaming | Austria | 47.9333, 15.1
Hebei | China | 39.8897, 115.275

This means that every row is a single string (VARCHAR) that contains some location, with the different fields separated by a pipe (|).

I would like to write a query that returns the following:

------- ---------- ---------------------------------
Gaming   Austria    Gaming | Austria | 47.9333, 15.1
Hebei    China      Hebei | China | 39.8897, 115.275

Which means I want 3 columns: for the city, for the country, and the original column.

While splitting the city is easy (combination of CHARINDEX and SUBSTRING), extracting the country seems to be more challenging. The tricky part is to know the length of the country field in the string, so it could be extracted using SUBSTRING.

I realize I might have to write a SPLIT function in T-SQL, but I'm not sure how to write one that returns the data as a record and not as a table.

Hints and/or solutions will be more than welcome.

Answer 1

You can pass a 3rd parameter, which specifies the first character to start looking from. That means you can extract country like this:

CHARINDEX(field, '|', CHARINDEX(field, '|')+1)

Answer 2

Just a matter of specifying the appropriate starting position and dynamically calculating the length based on the positions of the delimiters within the string - as d. shows above with some additional tweaking:

select substring(fieldName,0,charindex('|',fieldName,0)) as city, 
  substring(fieldName,charindex('|',fieldName,0)+1,( charindex('|',fieldName,(charindex('|',fieldName,0)+1)) - charindex('|',fieldName,0) - 1)) as country,
  right(fieldName, charindex('|',reverse(fieldName),0)-1) as coordinates

Note that you may want to compare this with a CLR-based split function, as well as a variety of other possibilities which are outlined in this other serverfault thread.