locked
Column With Ending Spaces RRS feed

  • Question

  • SQL Server 2008 R2<o:p></o:p>

    I need to produce a text file with records containing a defined length. One of them is a street address which must be 50 characters long. If it is shorter then that I need to have spaces at the end.

    The below statement correctly gives me the street address except that it does not contain the necessary trailing spaces. I have not been able to figure out why.

    left((CONVERT(varchar(50), apmast.fmstreet) + '                                                  ') ,50)

    Any help would be appreciated.


    Wednesday, April 11, 2012 1:48 PM

Answers

  • hi

    Don't use varchar.. for fixed size use char(50)

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Proposed as answer by M0nkeyMaster Wednesday, April 11, 2012 1:53 PM
    • Edited by SimpleSQL Wednesday, April 11, 2012 1:56 PM
    • Marked as answer by Scott_Hanebutt Wednesday, April 11, 2012 1:59 PM
    Wednesday, April 11, 2012 1:50 PM
  • (Just a guess, mind you.)  If you are using len(fmStreet) to figure out why, then the problem is that you are using len(fmStreet) to figure out why.  the "len" function does not include trailing spaces when evaluating the length of a string.  If you wish to know the length of the string including trailing spaces, then append a "marker" at the end of the string and subtract 1.  For instance:

    len(someString + 'x') - 1

    will give you the length of someString including trailing blanks.

    EDIT:

    Like, VT, I also wander if you should be using char(50) instead of varchar.




    Wednesday, April 11, 2012 1:50 PM

All replies

  • hi

    Don't use varchar.. for fixed size use char(50)

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Proposed as answer by M0nkeyMaster Wednesday, April 11, 2012 1:53 PM
    • Edited by SimpleSQL Wednesday, April 11, 2012 1:56 PM
    • Marked as answer by Scott_Hanebutt Wednesday, April 11, 2012 1:59 PM
    Wednesday, April 11, 2012 1:50 PM
  • (Just a guess, mind you.)  If you are using len(fmStreet) to figure out why, then the problem is that you are using len(fmStreet) to figure out why.  the "len" function does not include trailing spaces when evaluating the length of a string.  If you wish to know the length of the string including trailing spaces, then append a "marker" at the end of the string and subtract 1.  For instance:

    len(someString + 'x') - 1

    will give you the length of someString including trailing blanks.

    EDIT:

    Like, VT, I also wander if you should be using char(50) instead of varchar.




    Wednesday, April 11, 2012 1:50 PM
  • I didn't realize that len would not count trailing spaces. SQL Server Management Studio was also not showing the trailing spaces in the results window.

    Thank you both!

    Wednesday, April 11, 2012 2:00 PM
  • I didn't realize that len would not count trailing spaces. SQL Server Management Studio was also not showing the trailing spaces in the results window.

    To see trailing spacs in SSMS, you need a marker at the end as Kent suggested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 11, 2012 2:03 PM