Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    29

    Unanswered: Need to remove extra spaces in query output

    I'm using the following command:

    osql -E -n -d testdb -i testquery.qry -o "c:\Scripts\output.txt" -h-1 -w 500 -s ","

    With the following query (testquery.qry):

    SET NOCOUNT ON
    SELECT table1.column5, table2.column9
    FROM table1, table2
    WHERE table1.column4 = table2.column4
    AND table1.column1 != "NULL"
    ORDER BY table1.column5

    All of the columns are cast as char up to 50 characters.

    Even if only a field has a few characters, I get a lot of extra white space in my output. I want to get rid of those trailing spaces. I've tried SET ANSI_PADDING OFF, RTRIM(), and CAST(x AS VARCHAR(y)). I still get the same output. What am I doing wrong, what am I missing?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your datatypes are defined as char(50), which is a fixed-width datatype.

    I am surprised that casting the data as varchar(50) didn't solve the problem though. Perhapst the cast is retaining the trailing spaces.

    Try RTRIM(CONVERT(varchar(50), [YourColumn])).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    29
    CONVERT(VARCHAR(x), table.column) worked, but the RTRIM didn't.

    At least now I have management widths... for now I'm happy with that. If anyone else has any ideas how to trim the spaces or knows why its not working...

    Thanks for your help.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you mean RTRIM didn't work? It will trim the spaces, if CONVERT does not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    29
    Quote Originally Posted by blindman
    What do you mean RTRIM didn't work? It will trim the spaces, if CONVERT does not.
    COVERT let me reduce column width, but RTRIM isn't removing any spaces. My output is the same regardless of RTRIM.

    In another query as part of the same routine, I did use LTRIM on a COUNT of rows, and that worked. Not sure what's going on with the first query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •