You're running a jeans company. Your system is collecting detailed orders' data, but it's a bit archaic and it's storing the number of units ordered per size in a delimited string with 20 "fields", each taking up 4 characters. You want to normalize this data to make it easier to eventually analyze orders at size level.
Given this string:
declare @input varchar(80); set @input = ' 1 2 2 4 4 2 2 1 ';
I want to extract the values, and store them in a way that enables me to correlate a field's index with the corresponding size in the size scale, which might look like this:
-- ' 28 30 32 34 35 36 37 38 40 42 44 46 48 '
Here's the t-sql I came up with:
create function dbo.GetSizeBreakdown(@input varchar(80))
returns @result table (SizeIndex int, Units int)
as
begin
-- declare @result table (SizeIndex int, Units int);
-- declare @input varchar(80);
-- set @input = ' 1 2 2 4 4 2 2 1 ';
declare @fieldCount int = 20;
declare @fieldLength int = 4;
declare @index int = 0;
declare @fieldValue varchar(4);
while(@index < (@fieldCount))
begin
set @fieldValue = substring(@input, @index * @fieldLength + 1, @fieldLength)
if (isnumeric(@fieldValue) != 0)
begin
insert into @result
select @index + 1, cast(ltrim(@fieldValue) as int);
end;
set @index = @index + 1;
end;
-- select * from @result;
return;
end;
select * from dbo.GetSizeBreakdown(' 1 2 2 4 4 2 2 1 ');
Returns this:
SizeIndex Units
1| 7 1
2| 8 2
3| 9 2
4| 10 4
5| 12 4
6| 14 2
7| 15 2
8| 16 1
Which is perfect because from there I can put each quantity in its bucket, and call it a day.
I'm sure there's a better way of doing this though, even though it runs in 0ms. Right? Where, how and why can this function be optimized?
