Empty varchars not responding to !=""

[ajlisowski]

Hello, I have a process that pulls a flat file into mysql. Certain rows have a column that has a code value that is a string. Most rows do not. I have written a query to select the ones that do not

 

!=""

 

However the columns with the empty string are still appearing. I believe this is because there IS some sort of character in that column. When I look at the results in phpMyAdmin I see it gives the td for that empty column as

 

<td class="">

<br>

</td>

 

So Im guessing a new line character is being stored in the db? Any idea how to prevent this from getting in or how to query against its existance?

[AyKay47]

you can probably use str_replace to replace the newline character(s)

however without seeing you code its hard to say

[xyph]

When you insert the flatfile into your database, make sure to trim() the values, then check if they're empty before inserting.

[Pikachu2000]

Post your query string, and the code that displays the results. You can probably use either the MySQL TRIM() or PHP trim() function if there's whitespace in the field (preferably MySQL's function).

 

EDIT: Actually, if that turns out to be cause, you can run one query to trim all the existing values, then do as xyph suggested and TRIM() them on insert if you want to.

[ajlisowski]

Trimming worked. I have no idea why I didnt think of that. Turns out the binary character 0a was being left behind. I believe its fixed now. Thanks.

 

 

EDIT:

Just for giggles and in case it comes up, how would I run that query to modify it? I can not for the life of me select the rows that have the binary 0a in them.

 

I try WHERE BINARY(`code`)='0a' but that doesnt work. Obviously WHERE `code`='' wouldnt either.

[ignace]

column_value != '\n'

[fenway]

Or you use a REGEXP to check for a alphanumeric character...