3 comments

  1. — Good point. It’s good to realise that it’s an issue with SQL dataypes in general, not only varchar(max), as the following example shows:

    DECLARE @NUMBER FLOAT
    SET @NUMBER = 3 / 2
    SELECT @NUMBER
    GO
    — Result : 1

    DECLARE @NUMBER FLOAT = 1
    SET @NUMBER = @NUMBER * 3 / 2
    SELECT @NUMBER
    GO

    — Result: 1.5

    — Another way to solve the issue, it to make sure all variables are of the same type:

    DECLARE @L_HUGESTRING AS VARCHAR(MAX)
    DECLARE @X VARCHAR(MAX) = ‘X’
    SET @L_HUGESTRING = REPLICATE (@X, 5000) + REPLICATE (@X, 5000)
    SELECT LEN(@L_HUGESTRING)
    GO

    Robp Beantwoorden

  2. You’re absolutely right, thank you for your additional examples!

    marks Beantwoorden

  3. Thanks a lot for explaining how to fight the truncation of varchar(max) when concatenating! Very helpful article!!

    aedna Beantwoorden