Post reply

How to add multiple spaces to dynamic sql string?

  • June 15, 2015 at 6:48 am

    #318204

    I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:

    SELECT @SQL2 = COALESCE(@SQL2,'')

    + ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)

    That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.

  • June 15, 2015 at 6:58 am

    #1805435

    rc_stone_1 (6/15/2015)

    I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:

    SELECT @SQL2 = COALESCE(@SQL2,'')

    + ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)

    That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.

    Here you go:

    SELECT @SQL2 = COALESCE(@SQL2,'') + ' ,' + p.Attribute + SPACE(60 - LEN(P.Attribute)) + 'NUMERIC(12,3)' + CHAR(10)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • June 15, 2015 at 7:10 am

    #1805438

    Ah! Gracias!

Viewing 3 posts - 1 through 2 (of 2 total)

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