SQL Server/T-SQL needs to concatenate strings yet fit under 100 bytes long

Question

I have a business problem needing a technical solution developed.

I receive in SQL Server an array of 1-20 columns. I need to concatenate variable length strings into a result fitting in 100 bytes. The receiving system is unwilling to increase length yet wants as much data as will fit into 100 bytes.

This is not about joining nor using functions, this is a programming question about the logic of adding elements until a max length of 100 bytes and not chopping the last element which fits.

I am also concerned with looping 20 times per row, this may increase processing far beyond adding all strings until I find one empty. The input is organized by importance, and you cannot fill column 20 if 19 is blank. A common data might be to fill one or two columns and the rest are blank ('').

More than 99% of data fits because many rows have one or two values, and the other 18 columns are sparsely populated left to right. Recently I had the first time data went to exactly 100 bytes and chopped on the last byte was a comma delimiter resulting in an error on the handoff from my database to the receiving system.

I am currently using SQL Server 2012 planning an upgrade next year. The details of the code values are proprietary, so I will dummy some data with generic 3-4 byte values and pretend the string is lilmited to 11 bytes long. My actual source are varchar(7), so I can fit a reasonable number of codes into 100 bytes yet a heavy string of long values may result in truncating the last value.

As mentioned only the ending on byte 100 as a comma revealed an error and the receiver does not yet realize some low percentage of data is truncated.

Thank you for your time and consideration. Phil

sample result of my first try to be finetuned

Str	StrLen	id	id	id	Fixed	StrLen2
dddd,dddd,dddd	14	4	4	4	dddd,dddd	9
dddd,dddd,ccc	13	4	4	3	dddd,dddd	9
aaa,dddd,dddd	13	1	4	4	aaa,dddd	8
bbb,dddd,dddd	13	2	4	4	bbb,dddd	8
ccc,dddd,dddd	13	3	4	4	ccc,dddd	8
dddd,dddd,aaa	13	4	4	1	dddd,dddd	9
dddd,dddd,bbb	13	4	4	2	dddd,dddd	9

Sample data and SQL Code

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Phil Pham
-- Create date: 2022-09-07
-- Description: Concatenate strings keeping under a controlled length
-- =============================================

CREATE FUNCTION FcnConcatStrings 
(   @Str1 varchar(max)  -- base string

,   @Str2 varchar(max)  -- addition

,   @Delim  char(1) -- comma, tab, or other custom delimiter

,   @Len    int -- the max length

)
RETURNS varchar(max)
AS
BEGIN

    -- Declare the return variable here
    DECLARE @Result varchar(max)

    -- Add the base
    SELECT @Result = @Str1

    -- Add the addition, IFF not too long
    if len(@Result +@Delim +@Str2) <= @Len
        SELECT @Result = @Result +@Delim +@Str2

    -- Return the result of the function
    RETURN @Result

END
GO

;with temp (id, list) as
    (select 1,  'aaa'
    union select 2, 'bbb'
    union select 3, 'ccc'
    union select 4, 'dddd'
    )

Select Str=l1.list +','+ l2.list +','+ l3.list
    ,StrLen=len(l1.list +','+ l2.list +','+ l3.list)
    , l1.id, l2.id, l3.id  
From    temp l1,    temp l2,    temp l3
Where 1=1
--Order by 3,4,5 -- use this to show cross product order

Order by 2 desc -- use this to show problems too long

;with temp (id, list) as
    (select 1,  'aaa'
    union select 2, 'bbb'
    union select 3, 'ccc'
    union select 4, 'dddd'
    )

Select Str=l1.list +','+ l2.list +','+ l3.list

    ,StrLen=len(l1.list +','+ l2.list +','+ l3.list)

    , l1.id, l2.id, l3.id  

    ,Fixed=dbo.FcnConcatStrings( dbo.FcnConcatStrings(l1.list, l2.list, ',', 11), l3.list, ',', 11) 

    ,StrLen2=len(dbo.FcnConcatStrings( dbo.FcnConcatStrings(l1.list, l2.list, ',', 11), l3.list, ',', 11) )

From    temp l1,    temp l2,    temp l3

Where 1=1
--Order by 3,4,5 -- use this to show cross product order

Order by 2 desc -- use this to show problems too long

Charlieface · Answer

For when you upgrade to a more modern version of SQL Server:

You can use STRING_AGG and window functions to achieve this. It's hard to show exactly, because I'm unsure exactly what result you want, and how they should be concatenated.

All in a subquery:
Create a VALUES table of all the values you wish to concatenate, along with an ordering column.
Calculate a running sum of the length of each value.
Use that sum to work out how far to aggregate.
Aggregate only as much as you want.

SELECT
  t.Id,
  YourConcattedValue =
    @startingValue +
   (
    SELECT STRING_AGG(v.value, ',') WITHIN GROUP (ORDER BY v.ordering)
    FROM (
        SELECT *,
          RunningLength = SUM(LEN(v.value)) OVER (ORDER BY v.ordering ROWS UNBOUNDED PRECEDING)
        FROM (VALUES
            (1, t.Str1),
            (2, t.Str2),
            (3, t.Str3)
        ) v(ordering, value)
    ) v
    WHERE v.RunningLength + LEN(@startingValue) <= 100;
  )

FROM YourTable t;

SQL Server/T-SQL needs to concatenate strings yet fit under 100 bytes long

1 Answers