SQL switch latitude and longitude positions in the polygon string

I have a text that contains polygon information

Example data:

{"points":[{"lat":49.454912346,"lng":-123.1000021159},{"lat":47.454912346,"lng":-122.1000021159},{"lat":55.454912346,"lng":-121.1000021159},{"lat":52.454912346,"lng":-127.1000021159},{"lat":26.454912346,"lng":-129.1000021159}]

it there any way that could swap “lat” and “lng” in this string in SQL Server?

Expect outcome:

{"points":[{"lng":-123.1000021159,"lat":49.454912346}

Any help appreciate!

Answer

What you have here is JSON. You can break out the info using OPENJSON, swap it round, and rebuild it using FOR JSON:

DECLARE @json nvarchar(max) = '{"points":[{"lat":49.454912346,"lng":-123.1000021159},{"lat":47.454912346,"lng":-122.1000021159},{"lat":55.454912346,"lng":-121.1000021159},{"lat":52.454912346,"lng":-127.1000021159},{"lat":26.454912346,"lng":-129.1000021159}]'

SELECT j.lng, j.lat
FROM OPENJSON(@json, '$.points')
  WITH (lat decimal(18,10), lng decimal(18,10)) j
FOR JSON PATH, ROOT('points');

db<>fiddle

If you have a table that you want to select from, you would do it like this

SELECT
  (
    SELECT
    j.lng, j.lat
    FROM OPENJSON(t.json, '$.points')
      WITH (lat decimal(18,10), lng decimal(18,10)) j
    FOR JSON PATH, ROOT('points')
  ) AS json
FROM YourTable t;