Posted by Rik on 10/10/06 11:10

Squet34785 wrote:
> Hello,
> A bit off topic as it's about MySQL but I thought someone here would
> know the answer.
> I imported a list of records into my database from a tab delimited
> file, and where some columns should be null or empty strings the
> associated PHP script is treating them as valid entries. If I try to
> view the column:
>
> mysql> select variantspelling from works where uid2=12777;
> +-----------------+
>> variantspelling |
> +-----------------+
> |
> +-----------------+
> 1 row in set (0.00 sec)
>
> It appears to be a newline, as I can pull it out in PHP and wrap it
> with some characters and it only seems to a newline character.
> However the following doesn't work:
>
> mysql> select variantspelling from works where variantspelling regexp
> '\n' and uid2=122777;
> Empty set (0.00 sec)
>
> Nor does
>
> mysql> select variantspelling from works where variantspelling regexp
> '\n\r' and uid2=122777;
> Empty set (0.00 sec)
>
> Does anyone know what the characters might be and how to identify
> them in the database so I can mark them as null?

First, form the MySQL manual:
Note: Because MySQL uses the C escape syntax in strings (for example, �\n�
to represent the newline character), you must double any �\� that you use
in your REGEXP strings.

So you should use REGEXP '\\n'

What I'd do is the reverse, if it doesn't contain a single valid character
(for instance, [a-z0-9] is required for a minimum of one), set to null.
Very often it's more use searching for valids then searching for invalids,
as the ways a certain can be invalid will often be more numerous then the
way they can be valid.

- First check wether the query:
SELECT variantspelling
FROM works
WHERE variantspelling NOT REGEXP '[[:alnum:]]';
returns indeed all (and only) the matches that have to be updated.
- then run:
UPDATE works
SET variantspelling = NULL
WHERE variantspelling NOT REGEXP '[[:alnum:]]';

Grtz,
--
Rik Wasmus

[Back to original message]