My calculation with a CASE statement isn\'t working

  • I'm scratching my head over this. I'm sure it's something simple, but I'm stumped. I have the following T-SQL:

    DECLARE @MAX_naam AS varchar(MAX) = (SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22
    DECLARE @maxnaam_length AS int = LEN(@MAX_naam);
    DECLARE @num_zeroes AS int = 5 - @maxnaam_length;
    DECLARE @zeroes AS varchar =
    CASE WHEN @num_zeroes = 0 THEN ''
    WHEN @num_zeroes = 1 THEN '0'
    WHEN @num_zeroes = 2 THEN '00'
    WHEN @num_zeroes = 3 THEN '000'
    ELSE '' END;
    select @zeroes

    @MAX_naam is currently 22. @maxnaam_length is, correctly 2, and @num_zeroes is 3. I've verified each of these. However, despite expecting @zeroes to be '000', it comes out as '0'. Why?

  • DECLARE @zeroes AS varchar

    If you declare a variable or parameter as varchar w/o specifying length -- e.g., varchar(5) -- it is implicitly declared as varchar(1), and can only contain one character.

  • OK, you're right. However, when I change my code to DECLARE @zeroes AS varchar(5) I get the same result. @zeroes = '0'

  • I don't - I get '000' if @max_naam = '22'. Does @MAX_naam really need to be varchar(max)?

     

    DECLARE @MAX_naam AS varchar(MAX) = '22'
    DECLARE @maxnaam_length AS int = LEN(@MAX_naam);
    DECLARE @num_zeroes AS int = 5 - @maxnaam_length;
    SELECT @MAX_naam AS MAX_naam,@maxnaam_length AS maxnaam_length, @num_zeroes AS num_zeroes;

    DECLARE @zeroes AS VARCHAR(5) = CASE WHEN @num_zeroes = 0 THEN ''
    WHEN @num_zeroes = 1 THEN '0'
    WHEN @num_zeroes = 2 THEN '00'
    WHEN @num_zeroes = 3 THEN '000'
    ELSE '' END;
    select @zeroes

    What does this return?

    SELECT MAX(naam) AS max_naam, SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) AS max_naam_len FROM incident

     

  •  

    Rather than a CASE, use the built-in REPLICATE function:

    DECLARE @MAX_naam AS varchar(MAX) 
    SET @MAX_naam = 22 --(SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22
    DECLARE @maxnaam_length AS int = LEN(@MAX_naam);
    DECLARE @num_zeroes AS int = 5 - @maxnaam_length;
    DECLARE @zeroes varchar(5) = REPLICATE('0', @num_zeroes)

    SELECT @zeroes

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Are you just trying to LPAD zeros for a total length of 5?  If so, this will make your life easier...

    DECLARE @SomeInt INT = 22;
    SELECT RIGHT(CONCAT('00000',@SomeInt),5)
    ;

    Note that @SomeInt in the formula could actually be a column name from a table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Oh bless you, Scott Pletcher, this works wonderfully.

  • Melanie Peterson wrote:

    Oh bless you, Scott Pletcher, this works wonderfully.

    I have a question just out of curiosity... what are you going to do with the zeroes once you have the correct number of zeros returned as a string?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • DECLARE @naam AS varchar(20) = 'I' + RIGHT(CAST(DATEPART(yy,GETDATE()) AS varchar),2) + RIGHT('0'+CAST(DATEPART(mm,GETDATE()) AS varchar),2) + '-' + @zeroes + @MAX_naam;
  • This should give you the same result as your complicated calculation

    SELECT @naam = 'I' + CONVERT(char(4), GETDATE(), 12) + '-' + RIGHT('00000' + @MAX_naam, 5)
  • DesNorton, your solution is so much simpler. Thank you very much!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply