sql server - Split string returning a record -

I have a column table that looks like this:

  gaming | Austria | 47.9333, 15.1 Hebei China |  

This means that each line is a single string (VARCHAR), which contains some locations, with different fields by a pipe (|).

I would like to write a query which gives the following:

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

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

The city is easy to split (combination chrinedex and subbrassarring), it seems more challenging to remove the country, for hard part, know the length of the country's area in the string, so it should be removed using SUBSTRING. Could.

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

Signals and / or solutions will be more than welcome.

Specifying the correct starting position and dynamically depending on the position of the delimiter within the string A case of calculation- such as D. Shows up with some extra tweaking:

  substring (fieldName, 0, charindex ('|', fieldName, 0)) as a city, substring (fieldName, charindex ('|' , FieldName, 0) +1, (Chirendex ('|', Field Name, ('' ',', Field Name, 0) +1)) - Charindex ('|', Field Name, 0) - 1)) Note that, as a country, the right (field name, as well as it may want to compare it with a CLR-based partition function, charindex ('|', reverse (field name), 0 ) -1) As Coordinate  

Un Variety of possibilities that they are highlighted.