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

Transact-SQL
,
1

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

2

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