Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Removing char(13) + char(10) with SQL

    I have a bunch of email addresses that were accidentally imported with char(13) + char(10) even though the Bulk Insert code is using the default row terminator (newline). Anyway, I need to fix the data but my update statement doesn't seem to update any of them:

    UPDATE contacts SET
    emailAddress = RTRIM(replace(replace(emailAddress, CHAR(13), ''), CHAR(10), ''))
    WHERE emailAddress IN
    (
    SELECT RTRIM(replace(replace(emailAddress, CHAR(13), ''), CHAR(10), '')) AS EmailAddress
    FROM dbo.contacts
    GROUP BY RTRIM(replace(replace(emailAddress, CHAR(13), ''), CHAR(10), ''))
    HAVING(COUNT(RTRIM(replace(replace(emailAddress, CHAR(13), ''), CHAR(10), '')))>1)
    )

    I'm running MS SQL 2000. Can someone shed some light?

    TIA

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can try replacing your WHERE clause with this:
    ...WHERE charindex(char(13), emailAddress) > 0 or charindex(char(10), emailAddress)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov View Post
    You can try replacing your WHERE clause with this:
    ...WHERE charindex(char(13), emailAddress) > 0 or charindex(char(10), emailAddress) > 0
    Fixed a typo....
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Mar 2003
    Posts
    97
    I'm confused. If I run this:

    SELECT RTRIM(replace(emailAddress, CHAR(10), '')) AS EmailAddress
    FROM dbo.contacts
    GROUP BY charindex(char(10), emailAddress), emailAddress
    HAVING(COUNT(charindex(char(10), emailAddress))>1)

    I get 524 rows

    But, if I run this:

    UPDATE contacts SET
    emailAddress = RTRIM(replace(replace(emailAddress, CHAR(13), ''), CHAR(10), ''))
    WHERE charindex(char(10), emailAddress) > 0

    I get 0 rows.

    Why is that?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have 524 rows where emailAddress is not null, none of the 524 emailAddress values contain Char(10).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    group by charindex(char(10), emailAddress) returns 0 if 0D is not there, and 1 if it is, so you're grouping by 0/1 and emailAddress. There are 524 rolled up records.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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