Hall of Fame
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.
J. Drew Allen
Business Intelligence Analyst
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;';
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
Hall of Fame
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)