Post reply

FIXED LENGTH OF COLUMN

  • July 20, 2010 at 2:16 pm

    #72846

    Hi,

    I am using dts package to export data to text file..

    Query is.

    select

    '' as sno,

    name,

    comments

    from xyz

    Here I want to show data in text file in fixed length..

    length of variables

    sno - 3

    name 15

    comments 50

    Here in select statement SNO is '' but in text it has to take 3 spaces.

    Similarly what ever may be the length.. It has to take the given length.

    Please help me on this

    🙂

  • July 20, 2010 at 2:43 pm

    #1195802

    For more info.. attaching sample text document.. Hope you can undestand this..

    In attachement, the name column actual value in 2nd row is Guru govind singh...

    🙂

  • July 20, 2010 at 3:10 pm

    #1195817

    Have you tried this:

    select

    cast('' as char(3)) as sno,

    name,

    comments

    from xyz

  • July 20, 2010 at 3:27 pm

    #1195828

    Lynn Pettis..

    Thank You.. This is useful for me

    🙂

  • July 21, 2010 at 4:42 am

    #1196051

    Hi..

    I think we can meet your requirement by using REPLICATE function(i.e..Repeats a character expression a specified number of times)

    An updated query using REPLICATE is given below

    select sno+replicate(' ',datalength(sno)-len(sno) as sno

    ,name+replicate(' ',datalength(name)-len(name) as name

    ,comments+replicate(' ',datalength(comments)-len(comments) as comments

    from xyz

    I hope it works fine..

    [font="Comic Sans MS"]Praveen Goud[/font]

  • July 21, 2010 at 11:42 am

    #1196347

    Praveen Goud Kotha (7/21/2010)

    Hi..

    I think we can meet your requirement by using REPLICATE function(i.e..Repeats a character expression a specified number of times)

    An updated query using REPLICATE is given below

    select sno+replicate(' ',datalength(sno)-len(sno) as sno

    ,name+replicate(' ',datalength(name)-len(name) as name

    ,comments+replicate(' ',datalength(comments)-len(comments) as comments

    from xyz

    I hope it works fine..

    Praveen,

    Please change your avatar to something else. I made that avatar and would like to keep it for exclusive use. Thanks.

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

Viewing 6 posts - 1 through 5 (of 5 total)

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