Post reply

right fill string characters

  • May 12, 2004 at 10:21 am

    #152139

    I have created a table with char(x) fields.  I need to right fill the strings that are inserted into those fields.  How do I do that?  When I insert a record, the string data is filled left to right.  I want the data right aligned in the field.

     

  • May 12, 2004 at 2:05 pm

    #506205

    Well, here's one way to do it.....

    declare

     @string char(20),

     @input varchar(20),

     @len int

    set @input = 'example'

    set @string = ' '

    set @len = len(@input)

    select @string = reverse(stuff(@string, 1, @len, reverse(@input)))

    select @string

    Steve

  • May 12, 2004 at 4:25 pm

    #506218

    Wow!   That is tremendous Steve and it works great.  Thank you so much.  I have spent several hours in the online manual trying to figure a way. 

    -Doug

  • May 12, 2004 at 9:32 pm

    #506224

    Here's a different way...

    DECLARE @TestString VARCHAR(20)

    DECLARE @DesiredLEN TINYINT

    DECLARE @FillChar CHAR(1)

    DECLARE @ResultString VARCHAR(255)

        SET @TestString = 'SomeString'

        SET @DesiredLEN = 25

        SET @FillChar   ='#' --So you can "see" spaces... change to ' ' for production

     
        SET @ResultString = RIGHT(REPLICATE(@FillChar,@DesiredLEN)+@TestString,@DesiredLEN)
      PRINT @ResultString

    (You said "right fill" and my original post "filled the right" with "#".  Have repaired this code to work as "right justify" as you wanted).

    --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

  • May 13, 2004 at 6:26 am

    #506282

    Ahem...

    Much easier is this...

    Declare @MaxLen TinyInt

    Update dbo.Table Set dbo.Table.FieldName = Replicate('0', @MaxLen - Len(LTrim(RTrim(dbo.Table.FieldName)))) + LTrim(RTrim(dbo.Table.FieldName))

  • May 13, 2004 at 6:56 am

    #506294

    Another way is like:  SELECT RIGHT(SPACE(20) + 'example', 20).  Probably use a LEN function or variable instead of a scalar value though...

  • May 13, 2004 at 8:02 am

    #506321

    SELECT RIGHT(SPACE(20) + 'example', 20)

    That is amazingly simple and it works great for my need!   Where you have '20' I will use the width of my CHAR(x) field and of course where you have 'example' I will use my column name. 

    BTW, your example also works for padding with other characters:

    SELECT RIGHT('##########' + 'example', 10)

    My thanks to everyone's input. 

  • May 16, 2004 at 7:48 am

    #506578

    Hi all.

    Has anyone considered using the following?

    SELECT RIGHT(space(DATALENGTH("column name")) + "column name", DATALENGTH("column name"))

    from "table name"

    Um, replace "column name" and "table name" with your schema object names.

  • May 16, 2004 at 7:17 pm

    #506593

    Unless it was a text or ntext field, why would you use "DATALENGTH" over just "LEN" like some of the other replies used?   Just curious...

    --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

  • May 17, 2004 at 5:29 am

    #506612

    In case you have purposefull spaces. See what happens with these

    select len('a  ')

    select datalength('a  ')

    First will return 1 the second 3 becuase len ignores the trailing spaces.

  • May 17, 2004 at 5:50 am

    #506619

    Exactamundo Antares! 

    And!... this marvelous function (DATALENGTH) also returns the defined length of your table's columns. This has proved very handy in ETL work.

     

     

  • May 18, 2004 at 5:42 pm

    #506879

    Thanks Antares... didn't know that. 

    Hey paolice... how do you get DataLength to return the "defined length of your table's columns"?  Couldn't find a thing about that in BOL.

    --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

  • May 19, 2004 at 11:14 am

    #506993

    Hey Jeff,

      Yes I know this description of this function's capabilities is not found in BOL. I discovered it's 'hidden feature' after testing (my) conclusions that I drew from reading what the BOL does say about it.

    Trial and Error: it's not just for scientists anymore. 

  • May 19, 2004 at 5:18 pm

    #507044

    paolice,

    Understood... I use "trial and error" a lot.  But you still haven't given me a clue as to how to use DataLength to determine the defined length of a column.  Particularly interested in how you do it for a VARCHAR column... mind sharing?

    --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

Viewing 14 posts - 1 through 13 (of 13 total)

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