SQLTeam.com | Weblogs | Forums

An array to multiply with a constant with all elements in SQL

Hi,

I have a Transaction table with an arra/JSON field.

Ex: [24.00,36.76,43.52,69.04,84.13,8.62] etc... Its a long array with more than 300 elements.

Need to multiply 0.25 with all elements. And total no of rows will be millions. -- So its hard to do it manually. Is there any script used to update this field.

Regards,
Venkat

So that is not json data. When you say json column, do you mean varchar? what do you want the final result to look like?

use sqlteam
go

declare @mangolassi table(boombam nvarchar(max))
declare @multiplier float =  0.25

insert into @mangolassi
select '[24.00,36.76,43.52,69.04,84.13,8.62]'


create table #shimsham(tweet float)

;with src
as
(
select replace(replace(boombam, '[', ''), ']','') as trimtram
  from @mangolassi
), breakitdown as (
select item, round(cast(item as float) * @multiplier, 2) as multi
  from src
  cross apply DelimitedSplit8K(src.trimtram, ',')
  )
insert into #shimsham
select multi From breakitdown

select distinct '[' + STUFF((SELECT ', ' + cast(US.tweet  as varchar(50))
          FROM #shimsham US
          
          FOR XML PATH('')), 1, 1, '') + ']' [breezy]
  from #shimsham SS
drop table #shimsham
1 Like