Post reply

char(10) and char(13)

  • June 23, 2011 at 8:39 am

    #240462

    hI, i AM TRYING TO REMOVE emty by char(10) and char(13) and still see it.

    Here is my syntax:

    REPLACE(REPLACE(REPLACE(Col011, CHAR(10) + CHAR(13), ' '), CHAR(10), ' '), CHAR(13), ' ') AS Expr2

    can someone please help me

  • June 23, 2011 at 8:41 am

    #1343086

    CrLf is CHAR(13) + CHAR(10), i think you have the order reversed, so it's not finding anything.

    REPLACE(Col011, CHAR(13) + CHAR(10) , ' ') AS Expr2

    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!

  • June 23, 2011 at 9:03 am

    #1343109

    no stiil getting it

  • June 23, 2011 at 9:31 am

    #1343153

    Are you saying this doesn't work?

    replace( col011, CHAR(10), '')

    http://msdn.microsoft.com/en-us/library/ms186862.aspx

  • June 23, 2011 at 9:34 am

    #1343158

    Yes,it is not working, when I click on the data goes away,when I go to another record comes back

  • June 23, 2011 at 9:40 am

    #1343170

    Here is proof that "it works"

    DECLARE @a CHAR(1) = CHAR(10)

    SELECT @a, ASCII(@a), REPLACE(@a,CHAR(10),' '), ASCII(REPLACE(@a,CHAR(10),' '))

    Now please try to explain how your problem is different from what has been suggested.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 23, 2011 at 9:41 am

    #1343174

    So can you post all the code? REPLACE by itself doesn't change things. You would have to do this:

    update mytable

    set col011 = replace( col011, CHAR(10), '')

  • June 23, 2011 at 9:46 am

    #1343181

    are you sure its not an another type of blank space , like tab for example

    Jayanth Kurup[/url]

  • June 23, 2011 at 10:08 am

    #1343200

    DECLARE @a CHAR(1) = CHAR(10)

    SELECT @a, ASCII(@a), REPLACE(@a,CHAR(10),' '), ASCII(REPLACE(@a,CHAR(10),' '))

    This is working

    Thank you so much

  • November 6, 2014 at 11:29 am

    #1757067

    Using:

    replace( replace(CAST(FieldName AS NVarchar(MAX)), char(13), ''), char(10), '')

    Still experiencing column alignment issues when exporting to excel (?) Please what format can I use to avoid this?

  • March 17, 2017 at 7:22 am

    #1933736

    Go into Tools/Options/Query Results/SQL Server/Results to Grid - Check Retain CR/LF on copy or save.

    That should ensure your Char(10)'s move into Excel when you copy/paste.

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

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