Split string returning a record

Question

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.

Ugh, that's horrible table design :( Hope you can change it at some point. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 1, 2009 at 15:50

Community · Accepted Answer · 2017-04-13 12:13:35Z

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.

edited Apr 13, 2017 at 12:13

CommunityBot

11 silver badge

answered Dec 1, 2009 at 15:55

boydc7

4,64323 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moshe Over a year ago

Thanks very much, the CHARINDEX manipulation was the part I missed.

David Hedlund · Accepted Answer · 2009-12-01 15:48:45Z

0

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)

answered Dec 1, 2009 at 15:48

David Hedlund

130k33 gold badges205 silver badges223 bronze badges

3 Comments

Moshe Over a year ago

Thanks. But that would return the index, and not the length required by SUBSTRING.

David Hedlund Over a year ago

well, yes, that would give you the info about what parameters you need to pass to the substring. i got the impression you knew how to deal with that part already

Moshe Over a year ago

You're right, I do. For some reason all of those CHARINDEX calls confused me with too much numbers. Drawing the string on a board helped me out. :)

Collectives™ on Stack Overflow

Split string returning a record

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related