Post reply

Remove new line char from a varchar field.

  • September 3, 2004 at 10:48 am

    #60209

    I have a field(varchar) where there are new line chars throughout. Is there a replace statement which can remove the chars from this field?  Or is there a way to find\identify the NL chars using sql?

    Thank you.
    Francis S. Mazeika
    MS SQL DBA
    609-707-5207
    francis.mazeika@gmail.com

  • September 3, 2004 at 11:12 am

    #521696

    UPDATE YourTable

    SET YourCol = REPLACE(YourCol, CHAR(13) + CHAR(10), '')

    --
    Adam Machanic
    whoisactive

  • September 3, 2004 at 12:07 pm

    #521704

    Thank you very much it worked!

    Thank you.
    Francis S. Mazeika
    MS SQL DBA
    609-707-5207
    francis.mazeika@gmail.com

  • September 3, 2004 at 12:33 pm

    #521712

    Just adding to the brain dump, this worked very well, I found I had some line feeds to.

    These steps cleaned it all out.

    UPDATE mytable

       SET myfield = REPLACE(myfield, CHAR(9), '') WHERE CHARINDEX(CHAR(9), myfield) <> 0

    UPDATE mytable

       SET myfield = REPLACE(myfield, CHAR(10), '') WHERE CHARINDEX(CHAR(10), myfield) <> 0

    UPDATE mytable

       SET myfield = REPLACE(myfield, CHAR(13), '') WHERE CHARINDEX(CHAR(13), myfield) <> 0

    Thanks for your lead on this

    Thank you.
    Francis S. Mazeika
    MS SQL DBA
    609-707-5207
    francis.mazeika@gmail.com

  • February 23, 2005 at 3:03 pm

    #543792

    How do you this with a text column field? Can't use replace.

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

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