SQL Server: Using CHARINDEX to parse multiplying problems

Question

I have a data column that stores products distribution units as 1*1 or 1*2*6. I want to formulate a computed column and have the result of the multiply problem.

below is illustrated example

sku       du      computed_du
12345678  1*2     2
12345679  1*3     3
12345680  1*6*2   12

Is there a most effective way to perform this calculation on sql server?

have you considered storing data correctly (as individual columns) and removing the formula? — gbn, May 12 '11 at 04:51

score 2 · Accepted Answer · edited May 23 '17 at 12:04

This is definitely something that should be calculated elsewhere, but I have seen the below technique used as a crude product aggregation.

I am not positive about using this for computed column, but here is how you might pull it off: Wrap this in a function, schema bind it to your table, and reference it the computed column definition (not sure if mssql will allow this due to deterministic requirement of udf).

Uses split function from here.

declare @tab table (
    sku int,
    du varchar(10),
    computed_du int
)
insert into @tab
    select 12345678, '1*2', null union all
    select 12345679, '1*3', null union all
    select 12345680, '1*6*2*0', null

-- 
select sku, du, min(s), case when min(cast(s as int)) = 0 then 0 else exp(sum(log(cast(nullif(s, 0) as int)))) end
from @tab
cross
apply   dbo.Split('*', du)d
where cast(s as int)>0
group
by      sku, du;

SQL Server: Using CHARINDEX to parse multiplying problems

1 Answers1