Swapping coordinate pairs round

  • So I'm using SQL Server 2014 and I've been using Jeff Moden's excellent string splitting functions with a tally table to split up comma or semi-colon separated strings. I'm trying to switch round coordinate pairs within a string and think it must be possible to do it with that but can't figure out how...

    Here is a sample string: -


    It contains 5 pairs of values which have been coded the wrong way round ( x and y in the wrong order) - I have a table full of these in a column. I need to change the sample above to read...


    so I can write back to the table a corrected set of coordinates.

    Any suggestions how I manage that would be gratefully received.

    Many thanks 🙂

  • I don't use Jeff's string splitter, but this is the broad outline.

    1. Convert the item number to zero-based instead of one-based (if necessary).

      1. That is, subtract one from the item number (if necessary).

    2. Divide the converted item number by two to get the group number.
    3. Take the modulo base 2 of the original item number to get the new order within the group.
    4. Order by the group number and the new order within the group.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Needs the XML trick to reaggregate the string

    declare @string varchar(max)='-0.326548;51.75514;-0.32658;51.75507;-0.326261;51.755;-0.326231;51.75508;-0.326548;51.75514;';

    select *
    from dbo.DelimitedSplit8K_LEAD(@string, ';') ss
    cross apply (values (iif(ss.ItemNumber%2=0, ss.ItemNumber-1, ss.ItemNumber+1))) v(new_seq);

    select stuff((select ' ' + ss.Item
    from dbo.DelimitedSplit8K_LEAD(substring(@string, 1, len(@string)-1), ';') ss
    cross apply (values (iif(ss.ItemNumber%2=0, ss.ItemNumber-1, ss.ItemNumber+1))) v(new_seq)
    order by v.new_seq
    for xml path('')), 1, 1, '');

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Many thanks Drew for the suggestion and many thanks Steve - that was exactly what I needed.

    You guys are stars!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply