T-SQL Put in a field the average of hyphen separated values present in another field

Question

I have a table with like this:

ID  |  FIX_1 | FTO   | FIX_2                      |
_____________________________________________________
1   |        | 15452 |1.3-1.7-1.8-2.4-2.0         |
2   |        | 15454 |1.4-1.1-1.4-2.7-2.6-1.8-2.4 | 
3   |        | 15454 |1.9-1.3-1.3                 | 
....  ......   ....    .................
....  ......   ....    .................
100 |        | 15552 |0.4-1.7-1.2-2.1-2.6-1.6     |

I need do a select with FIX_1 field equal to the average of the hyphen separated values in FIX_2 field. Is it possible with T-SQL without use of temporary table? Thanks in advance

Why no temporary table? If you can't have a temp table I'm assuming you can't create functions? — iamdave, Feb 07 '17 at 14:03
The FIX_1 field output must be: 1.8 for the record with id = 1 , 1.9 for the record with id = 2 , 1.5 for the record with id = 3 and 1.6 for the record with id = 100 — Micheal Bolton, Feb 07 '17 at 14:07
This table violates the 1st normal form. *Why* don't you store the separated values in another table? Anyway, SQL Server 2016 offers the `STRING_SPLIT` method to split a string by a delimiter and return one row for each result — Panagiotis Kanavos, Feb 07 '17 at 14:09
What is the *real* problem you want to solve? Calculating averages on the fly and comparing is going to be *slow* - the server will have to scan the entire table. Storing the values properly in a separate table would be faster with proper indexing - even if the server had to calculate all averages, it could use an index on `FIX_1` to find a match but ... — Panagiotis Kanavos, Feb 07 '17 at 14:12
Finally, averages result in fractional digits. Most likely there will be **NO** row with the same average. Searching for the nearest match would make more sense. The average for Row #2 is 1.914285, returned by `select avg(cast(value as decimal(6,2))) from string_split('1.4-1.1-1.4-2.7-2.6-1.8-2.4','-')` — Panagiotis Kanavos, Feb 07 '17 at 14:14

score 2 · Answer 1 · answered Feb 07 '17 at 14:19

Option with a UDF

Declare @YourTable table (ID int,FIX_1 money,FTO int,FIX_2 varchar(max))
Insert Into @YourTable values
(1,null,15452,'1.3-1.7-1.8-2.4-2.0'),
(2,null,15454,'1.4-1.1-1.4-2.7-2.6-1.8-2.4'),
(3,null,15454,'1.9-1.3-1.3')

Update @YourTable Set FIX_1=B.Value
 From  @YourTable A
 Cross Apply (
                Select Value = Avg(cast(RetVal as money))
                 From (Select * from [dbo].[udf-Str-Parse](A.FIX_2,'-')) B1
             ) B

Select * From @YourTable

Option without a UDF

Update @YourTable Set FIX_1=B.Value
 From  @YourTable A
 Cross Apply (
                Select Value = Avg(cast(RetVal as money))
                 From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.FIX_2,'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                      ) B1
             ) B

Both would Return

ID  FIX_1   FTO     FIX_2
1   1.84    15452   1.3-1.7-1.8-2.4-2.0
2   1.9142  15454   1.4-1.1-1.4-2.7-2.6-1.8-2.4
3   1.50    15454   1.9-1.3-1.3

The UDF if Needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

are you're referencing a post or answer from @Shnugo, if so I guess this needs proper attribution?! if not no problem. — Tanner, Feb 07 '17 at 14:35
@Tanner Actually this is one of my parse utilities, but Shnugo suggested a XML safe parse in another post (which I picked-up on). This is a tip-of-the-hat to him. — John Cappelletti, Feb 07 '17 at 14:41
no problem, it looked like an attempt at attribution but it wasn't clear — Tanner, Feb 07 '17 at 14:43
@Tanner This was the actual link http://stackoverflow.com/questions/41999151/split-semicolon-delimiter-sql-to-rows/41999800#41999800 — John Cappelletti, Feb 07 '17 at 14:55

Charles Bretana · Answer 2 · 2017-02-07T14:55:24.503

0

with a user defined function...

create FUNCTION dbo.AvgOfDashSepVals ( @vals varchar(500))
returns float as
BEGIN
declare @avg decimal
declare @cnt int = 0
declare @sum float = 0.0
While charIndex('-', @vals) > 0 Begin
      if isnumeric(left(@vals, charIndex('-', @vals)-1)) = 0 
         return null          
    set @cnt+= 1
    set @sum += cast(left(@vals, charIndex('-', @vals)-1) as float)
    set @vals = substring(@vals, charIndex('-', @vals)+1, len(@vals))
End
RETURN case @cnt when 0 then null else @sum / @cnt end

then alter your table to add a computed column.

alter table myTable
   add Fix_1 as ([dbo].[AvgOfDashSepVals]([Fix_2]))

edited Feb 07 '17 at 14:55

answered Feb 07 '17 at 14:21

Charles Bretana

143,358
22
150
216

Good job! I've created a function like your udf but when I try to select dbo.AvgOfDashSepVals([FIX_2]) I got the message 'Divide by zero error encountred'. Do you know why? Thanks – Micheal Bolton Feb 07 '17 at 14:46
probably because it's running on an empty string (`@cnt` = 0) I added code to handle that. – Charles Bretana Feb 07 '17 at 14:50
Wonderful! It's almost perfect but when in the FIX_2 field there is only one value the function return NULL. Is it possible to truncate from third digit after the decimal point on the scalar result,furthermore? – Micheal Bolton Feb 07 '17 at 15:12
Ok I replaced null with @vals in the RETURN statement, but some returned values are incorrect. For example FIX_2 = 1.6-1.6-1.8-1.8-2.0-2.0-2.3-2.3 give 1.87142857142857 instead of 1.925. Do you know why? – Micheal Bolton Feb 07 '17 at 15:28
Bug when only one value in string. Fixed it... Should work now (Don't we always say that?) – Charles Bretana Feb 07 '17 at 16:22

score 0 · Answer 3 · answered Feb 07 '17 at 16:05

Declare @YourTable table (ID int,FIX_1 money,FTO int,FIX_2 varchar(max))
    Insert Into @YourTable values
    (1,null,15452,'1.3-1.7-1.8-2.4-2.0'),
    (2,null,15454,'1.4-1.1-1.4-2.7-2.6-1.8-2.4'),
    (3,null,15454,'1.9-1.3-1.3'),
    (4,null,15454,'1.5')

;WITH cte AS
(
    SELECT ID, SUBSTRING(FIX_2, 1, CHARINDEX('-',FIX_2) - 1) AS VALUE, SUBSTRING(FIX_2, CHARINDEX('-',FIX_2) + 1, LEN(FIX_2)) AS NEW_FIX_2
    FROM @YourTable
    WHERE CHARINDEX('-',FIX_2) > 0
    UNION ALL
    SELECT cte.ID, SUBSTRING(NEW_FIX_2, 1, CHARINDEX('-',NEW_FIX_2) - 1) AS VALUE, SUBSTRING(NEW_FIX_2, CHARINDEX('-',NEW_FIX_2) + 1, LEN(NEW_FIX_2)) AS NEW_FIX_2
    FROM @YourTable y
        JOIN cte ON cte.ID = y.ID
    WHERE CHARINDEX('-', NEW_FIX_2) > 0
    UNION ALL
    SELECT ID, NEW_FIX_2, NULL 
    FROM cte
    WHERE CHARINDEX('-', NEW_FIX_2) = 0
)
SELECT t.ID, ISNULL(v.VALUE, t.FIX_2) AS FIX_1, t.FTO, t.FIX_2
FROM @YourTable t
    LEFT JOIN (
        SELECT cte.ID, AVG(CAST(cte.VALUE AS MONEY)) AS VALUE
        FROM cte
        GROUP BY cte.ID
) v ON v.ID = t.ID

T-SQL Put in a field the average of hyphen separated values present in another field

3 Answers3