 | | | Subject: Re: ascii nulls in regex 's | Subject: Re: ascii nulls in regex 's 2007-10-26 - By Baron Schwartz
Back
Hi,
Wagner, Chris (GEAE, CBTS) wrote:
> Greetings all. I've run into something annoying that doesn't seem to be
> in the MySQL manual. I have fields of type varchar that contain null
> characters, chr(0). I need to find these for error reporting however
> MySQL seems to regard them as string terminators. A regex stops parsing
> on encountering the null. The fields should only contain DNS legal
> characters so I used a simple regex to find exceptions. However the
> nulls totally blow it up:
>
> mysql> SELECT "comprm1 " REGEXP "^[a-z0-9.-]+$";
> +-- ---- ---- ---- ---- ---- ---- --+
> | "comprm1 " REGEXP "^[a-z0-9.-]+$" |
> +-- ---- ---- ---- ---- ---- ---- --+
> | 0 |
> +-- ---- ---- ---- ---- ---- ---- --+
> 1 row in set (0.00 sec)
>
> mysql> SELECT "comprm1\0 " REGEXP "^[a-z0-9.-]+$";
> +-- ---- ---- ---- ---- ---- ---- ----+
> | "comprm1\0 " REGEXP "^[a-z0-9.-]+$" |
> +-- ---- ---- ---- ---- ---- ---- ----+
> | 1 |
> +-- ---- ---- ---- ---- ---- ---- ----+
> 1 row in set (0.00 sec)
>
>
> They also don't fall under the [:cntrl:] class!
> mysql> SELECT "comprm1\0 " REGEXP "[[:cntrl:]]";
> +-- ---- ---- ---- ---- ---- ---- --+
> | "comprm1\0 " REGEXP "[[:cntrl:]]" |
> +-- ---- ---- ---- ---- ---- ---- --+
> | 0 |
> +-- ---- ---- ---- ---- ---- ---- --+
> 1 row in set (0.00 sec)
>
> I can use LIKE to find them but I'ld prefer to use a single concise
> regex.
> mysql> SELECT "comprm1\0 " LIKE "%\0%";
> +-- ---- ---- ---- ---- ---+
> | "comprm1\0 " LIKE "%\0%" |
> +-- ---- ---- ---- ---- ---+
> | 1 |
> +-- ---- ---- ---- ---- ---+
> 1 row in set (0.00 sec)
>
>
> Any ideas?
Someone asked a similar question a few days ago. I'm not sure how well
it worked, but I suggested using INSTR() with CHAR(). Maybe there are
more details in that thread in the mailing list archives.
Baron
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=mysql@(protected)
|
|
|