ARRRG mysql data problem

[hep2djive]

I have a wierd scenario with some data in my db.

I have an email list program that I can enter addresses one at a time or import.

For some reason the email entered one at a time are fine, the imported ones have problems. I think there may be hidden data or something in the field.

example
If I do this I get NOTHING
SELECT * FROM `sonicmailer_pro_list` WHERE `address` = 'craig@test.com'

If I do this I get the correct record.
SELECT * FROM `sonicmailer_pro_list` WHERE `address` LIKE 'craig@test.com%'


When I look at the data, I do not see any trailing spaces. I even tried adding a TRIM() to double check.

The field is a varchar255.

Any idea how I can see whats going on?

Thanks

Craig

 

[r937]

select concat(address,'$') as a
  from sonicmailer_pro_list
 where address LIKE 'craig@test.com%'

then examine the result to see what's in front of the $


r937.com | rudy.ca

 

[hep2djive]

THANK YOU so much.... Looks like it may me a CR

As you see below the result for (a) was the email address and the $ was in position 1 of the next line.

a
gfeeback@comcast.net
$


Now I have several of these. Any clue of how to clean them up?

Again, thanks mucho

 

[r937]

use an UPDATE statement with the REPLACE function, search for CHAR(10) and CHAR(13) and replace them with empty strings

r937.com | rudy.ca

 

[hep2djive]

I hate to admit this, but I have no clue of how to write the statement to do this. I have several records in the table that have this.

Would you terribly mind giving me a hand

 

[hep2djive]

found the replace statement and cleaned them up, 1422 rows affected and fixed with your help..

You ROCK....

 

[r937]

update sonicmailer_pro_list
   set address
     = replace(
       replace(address,char(10),'')
               ,char(13),'')
 where address like concat('%',char(10),'%')
    or address like concat('%',char(13),'%')

r937.com | rudy.ca