Re: ascii nulls in regex's

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/[email protected]