Format json SQL using Replace command

Question

I want to change the format from r1 to r2 Using SQL Server

r1 :[ [ [ 1, 1 ], [ 2, 2 ], [ 3, 3 ],[ 4, 4 ] ] ]

r2 (should be ) : (1 1, 2 2), (3 3, 4 4)

ps: the r1 is a JSON coordinates (multilinestring) that this format is exactly stored in SQL.. the number that I put just for reduce the confusion,

My code

 declare @r1 varchar(max)= '[  [  [ 1, 1 ], [ 2, 2 ],  [ 3, 3 ],[ 4, 4 ]  ]  ]'
declare @r2 varchar(max) = replace(replace(replace(@r1,' ',''), '[','('), ']',')')
select @r2

the result doesn't give me the format that I need.

Why do you replace spaces, if your "should be" keep them? Maybe you should remove '[ ' and ' ]' — James
– James, Commented Dec 3, 2021 at 10:30

Charlieface · Accepted Answer · 2021-12-03 11:54:49Z

2

If you have valid JSON, you should use JSON functions to parse it

DECLARE @r1 nvarchar(max) = N'[ [ [ 1, 1 ], [ 2, 2 ], [ 3, 3 ],[ 4, 4 ] ] ]';

SELECT STRING_AGG(CONCAT(
  '(',
  JSON_VALUE(value, '$[0]'),
  ' ',
  JSON_VALUE(value, '$[1]'),
  ', ',
  JSON_VALUE(nextValue, '$[0]'),
  ' ',
  JSON_VALUE(nextValue, '$[1]'),
  ')'
  ), ', ')
FROM (
    SELECT
      j2.[key],
      j2.value,
      nextValue = LEAD(j2.value) OVER (ORDER BY j2.[key])
    FROM OPENJSON(@r1) j1
    CROSS APPLY OPENJSON(j1.value) j2
) j
WHERE CAST([key] AS int) % 2 = 0;

db<>fiddle

answered Dec 3, 2021 at 11:54

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

Format json SQL using Replace command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related