Thread: PL/PGSQL Regexe

I'm doing a check within a PL/PGSQL function using a regular expression 
and I get the error:

ERROR:  regcomp failed with error invalid character range

This appears to be because of the underscore "_" character. The regex I 
want to use is ''[A-Za-z0-9_]'' but this doesn't work. Any suggestions 
or is this a bug/feature?

rdear <rdear@dev.internal> writes:
> I'm doing a check within a PL/PGSQL function using a regular expression 
> and I get the error:

> ERROR:  regcomp failed with error invalid character range

> This appears to be because of the underscore "_" character. The regex I 
> want to use is ''[A-Za-z0-9_]'' but this doesn't work.

Seems to work for me:

regression=# select 'a' ~ '[A-Za-z0-9_]';?column?
----------t
(1 row)

regression=# select '_' ~ '[A-Za-z0-9_]';?column?
----------t
(1 row)

regression=# select ':' ~ '[A-Za-z0-9_]';?column?
----------f
(1 row)

How old is your Postgres?  (I can tell by the spelling of the error
message that it's not current.)
        regards, tom lane

Humm... 7.0.2, I'll upgrade and try again.<br /><br /> Thanks!<br /><br /><br /> Tom Lane wrote:<br /><blockquote
cite="mid:28279.1000909202@sss.pgh.pa.us"type="cite"><pre wrap="">rdear <a class="moz-txt-link-rfc2396E"
href="mailto:rdear@dev.internal"><rdear@dev.internal></a>writes:<br /></pre><blockquote type="cite"><pre
wrap="">I'mdoing a check within a PL/PGSQL function using a regular expression <br />and I get the error:<br
/></pre></blockquote><blockquotetype="cite"><pre wrap="">ERROR:  regcomp failed with error invalid character range<br
/></pre></blockquote><blockquotetype="cite"><pre wrap="">This appears to be because of the underscore "_" character.
Theregex I <br />want to use is ''[A-Za-z0-9_]'' but this doesn't work.<br /></pre></blockquote><pre wrap=""><br
/>Seemsto work for me:<br /><br />regression=# select 'a' ~ '[A-Za-z0-9_]';<br /> ?column?<br />----------<br /> t<br
/>(1row)<br /><br />regression=# select '_' ~ '[A-Za-z0-9_]';<br /> ?column?<br />----------<br /> t<br />(1 row)<br
/><br/>regression=# select ':' ~ '[A-Za-z0-9_]';<br /> ?column?<br />----------<br /> f<br />(1 row)<br /><br />How old
isyour Postgres?  (I can tell by the spelling of the error<br />message that it's not current.)<br /><br />
regards,tom lane<br /><br />---------------------------(end of broadcast)---------------------------<br />TIP 2: you
canget off all lists at once with the unregister command<br />    (send "unregister YourEmailAddressHere" to <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)<br
/></pre></blockquote><br/>