Swapping coordinate pairs round

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  March 23, 2023 at 11:19 am

  #4164083

  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: -

  -0.326548;51.75514;-0.32658;51.75507;-0.326261;51.755;-0.326231;51.75508;-0.326548;51.75514;

  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...

  51.75514;-0.326548;51.75507;-0.32658;51.755;-0.326261;-51.75508;0.326231;51.75514;-0.326548;

  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 🙂

• drew.allen

  SSC Guru

  Points: 76997

  More actions

  March 23, 2023 at 1:22 pm

  #4164150

  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.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Steve Collins

  SSCrazy

  Points: 2174

  More actions

  March 23, 2023 at 1:42 pm

  #4164188

  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

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  March 23, 2023 at 3:45 pm

  #4164225

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

  You guys are stars!