how to remove spaces

  • When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?

  • Post your query.

    Tell us how you got the results to your text file

    Gerald Britton, Pluralsight courses

  • my query

    DECLARE @Curr_Date DATETIME;

    --Set @Curr_Date = CURRENT_TIMESTAMP;

    set @Curr_Date = '2015-06-03 00:00:00.000'

    SELECT RTrim(c.REFERENCE_NO) + '|', 'HBBCKGRND' + '|' AS Literal , 'Refund Check Sent', RTrim([DESCRIPTION]) + ', ' + LTrim([TRANS_NBR]) + ', ' + LTrim([BANK_CHK_AMT]) + ', ' + convert(char(10),[CHECK_DATE],101) + ', ' + 'Refund check sent to ' + rtrim(PAYEE_NAME) AS [Free Text] ,+ '||||'

    FROM CBPAYMENT c

    where c.CASH_CODE = 101 and convert(varchar(10), c.CHECK_DATE,101) = convert(varchar(10), @Curr_Date, 101)

    order by c.CHECK_DATE desc

    when i open the text file, there seems to be a bunch of blank rows.

  • Since you are getting a lot of blank rows it seems like results are present but not being displayed. If I had to guess it is because there are logic problems in the strings that are being created in your columns. If any part of a string has an error the entire string will not get displayed.

    Can you try the query removing the concatenation just returning each part and see if you get results? If that works then start building your columns to see where things start breaking.

    Joie Andrew
    "Since 1982"

  • because you are appending fields to each other, if any value is NULL, when sent to a text file, you end up with blank rows.

    i'd start with bulletproofing the query for nulls, somethignl like this?

    DECLARE @Curr_Date DATETIME;

    --Set @Curr_Date = CURRENT_TIMESTAMP;

    SET @Curr_Date = '2015-06-03 00:00:00.000'

    SELECT Rtrim(ISNULL(c.REFERENCE_NO,'')) + '|',

    'HBBCKGRND' + '|' AS literal,

    'Refund Check Sent',

    Rtrim(ISNULL([DESCRIPTION],'')) + ', '

    + Ltrim(ISNULL([TRANS_NBR],'')) + ', '

    + Ltrim(ISNULL([BANK_CHK_AMT],'')) + ', '

    + CONVERT(CHAR(10), ISNULL([CHECK_DATE],''), 101) + ', '

    + 'Refund check sent to ' + Rtrim(ISNULL(PAYEE_NAME,'')) AS [free text],

    +'||||'

    FROM CBPAYMENT c

    WHERE c.CASH_CODE = 101

    AND CONVERT(VARCHAR(10), c.CHECK_DATE, 101) = CONVERT(VARCHAR(10), @Curr_Date, 101)

    ORDER BY c.CHECK_DATE DESC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • thank you guys but I noticed when I opened up my query in notpad ++ it shows carriage returns (CR) in the file.

    for example,my ouput is below (i added the CR myself so you can see what I see in notepad++) How do I make it in SQL managment studio to not tell me how many rows are showing? I also need to remove those extra CR at the end of my file.

    |literal||free text|CR

    1021276||HBBCKGRND||Refund Check Sent|4681419, 2100027, 14841.77, 06/03/2015, Refund check sent to Mutual of Omaha/State Farm We|||||

    508119||HBBCKGRND||Refund Check Sent|4681418, 2100031, 145.50, 06/03/2015, Refund check sent to BLUE CROSS|||||

    504675||HBBCKGRND||Refund Check Sent|4681415, 2100030, 369.95, 06/03/2015, Refund check sent to MICHAEL HELD|||||

    1006306||HBBCKGRND||Refund Check Sent|4681414, 2100026, 46.43, 06/03/2015, Refund check sent to MARZENA PLEBANEK|||||

    1098688||HBBCKGRND||Refund Check Sent|4681416, 2100028, 36.00, 06/03/2015, Refund check sent to MARYYYYYYYYYYYYYYYYYYYYYY FRAN|||||

    470413||HBBCKGRND||Refund Check Sent|4681417, 2100029, 165.62, 06/03/2015, Refund check sent to AETNA|||||

    CR

    (6 row(s) affected)

    CR

  • In SSMS go to "Query" > "Query Options" > "Advanced", select the checkbox "SET NOCOUNT" and click "OK".

    Joie Andrew
    "Since 1982"

  • beautiful! thank you.. 1 more question please..

    In notepad++, there is anothor blank row with a CR..how do I remove that in sql studio?

    |Literal||Free Text|

    1021276||HBBCKGRND||Refund Check Sent|4681419, 2100027, 14841.77, 06/03/2015, Refund check sent to Mutual of Omaha/State Farm We|||||

    508119||HBBCKGRND||Refund Check Sent|4681418, 2100031, 145.50, 06/03/2015, Refund check sent to BLUE CROSS|||||

    504675||HBBCKGRND||Refund Check Sent|4681415, 2100030, 369.95, 06/03/2015, Refund check sent to MICHAEL HELD|||||

    1006306||HBBCKGRND||Refund Check Sent|4681414, 2100026, 46.43, 06/03/2015, Refund check sent to MARZENA PLEBANEK|||||

    1098688||HBBCKGRND||Refund Check Sent|4681416, 2100028, 36.00, 06/03/2015, Refund check sent to MARYYYYYYYYYYYYYYYYYYYYYY FRAN|||||

    470413||HBBCKGRND||Refund Check Sent|4681417, 2100029, 165.62, 06/03/2015, Refund check sent to AETNA|||||

  • I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?

  • I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?

    If your extra carriage return is before or after your result set and not in the row data itself I do not think using REPLACE in the query will help you.

    Where in your file is the extra blank line? Also, what does the extra line matter? Does it break something?

    Joie Andrew
    "Since 1982"

  • wallywizard (6/25/2015)


    I read about using the REPLACE command with char(13) AND char(10), but I dont know how to use this. Can anyone please help me?

    REPLACE(YourString, CHAR(10), '') would replace the linefeed characters with nothing. This would be just like deleting the linefeeds.

    REPLACE(YourString, CHAR(13), '') would do the same for carriage returns.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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