ascii nulls in regex"s

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?






--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org