regular expression question

--- Joel Rodrigues <borgempath@Phreaker.net> wrote:

Hi,
I'm trying to write a check on a column restricting
the data to
numerals from 0-9, commas, dashes '-' and single
spaces. To
allow the entry of something like "134-140,
310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

'^[0-9, -]*$'

The ordering inside the square brackets doesn't
matter, except that the "-" needs to be somewhere
where it cannot be mistaken for a range operator. The
conditions for that vary according to the
implementation, but I think the above should work with
PostgreSQL.

Change the "*" to "+" if you want to require something
to be entered.

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

Did you try the obvious:
'^[0-9|, -]{0,10}$'

Wes Sheldahl

Joel Rodrigues <borgempath@Phreaker.net>@postgresql.org on 06/04/2003
02:40:18 PM

Sent by: pgsql-general-owner@postgresql.org

To: pgsql-general@postgresql.org
cc:
Subject: [GENERAL] regular expression question

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To
allow the entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

Hope you can help !

Cheers,
Joel

First, I'd suggest using the SQL99 SIMILAR functionality, since it's
more portable.

select '028-381, 283-183' similar to '[0-9|, -]+';
t

{0,10} isn't supported, but you'd be best doing that just by defining
the field to only be 10 characters. If that's not an option,
char_length(field) <= 10 should also work.

On Thu, Jun 05, 2003 at 12:10:18AM +0530, Joel Rodrigues wrote:

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To allow the
entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

Hope you can help !

Cheers,
Joel

--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Thu, Jun 05, 2003 at 00:10:18 +0530,
Joel Rodrigues <borgempath@Phreaker.net> wrote:

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To
allow the entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

How about:
'^[-0-9, ]*$'

On Thu, 5 Jun 2003, Joel Rodrigues wrote:

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To
allow the entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

Try this:

'^[[:digit:][:space:],-]{0,10}$'

On Thu, Jun 05, 2003 at 12:10:18AM +0530, Joel Rodrigues wrote:

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To allow the
entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

I don't think you can do what you want with a single regular expression.
You'll probably need to use multiple constraints: one to check for the
valid characters and the maximum length, and another to check that there
aren't two spaces together,

regression=# create table dos (a text, constraint dos_a_uno check (a ~
'^[a-z ]{0,10}$'), constraint dos_a_dos check (a !~ ' '));
CREATE TABLE
regression=# insert into dos values ('aaba');
INSERT 17091 1
regression=# insert into dos values ('aaba ');
INSERT 17092 1
regression=# insert into dos values ('aaba ');
ERROR: ExecInsert: rejected due to CHECK constraint "dos_a_dos" on "dos"

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo l�gico y coherente. Pero el universo real se halla siempre
un paso m�s all� de la l�gica" (Irulan)

On Thu, Jun 05, 2003 at 02:05:14 -0400,
Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:

On Thu, Jun 05, 2003 at 12:10:18AM +0530, Joel Rodrigues wrote:

Hi,
I'm trying to write a check on a column restricting the data to
numerals from 0-9, commas, dashes '-' and single spaces. To allow the
entry of something like "134-140, 310-312".

I seem to have got everything but the single spaces.

'^[0-9|,-]{0,10}$'

I don't think you can do what you want with a single regular expression.
You'll probably need to use multiple constraints: one to check for the
valid characters and the maximum length, and another to check that there
aren't two spaces together,

I don't know that it mattered for the original question, but it is surely
possible to allow only one consectutive space. There are a couple of
different ways to do this depending on exactly what you want. But probably
the most common also prohibits leading and trailing spaces and doesn't
allow an empty string. It should be easy to see how to change things for
different requirements.

'^([-0-9,]+( [-0-9,]+)*)?$'

On Thursday, June 5, 2003, at 01:14 , Jeff Eckermann wrote:

'^[0-9, -]*$'

Thanks Jeff, Wes, Jim, Bruno ! And no, simply putting a space
wasn't really obvious to me :-)

Try this:

'^[[:digit:][:space:],-]{0,10}$'

Scott, thanks, that's more like what I had in mind, seems more
robust (idiot-proof).

I don't think you can do what you want with a single regular
expression.

Alvaro, you're right in that if I want to be exacting, rather
than simply keeping obviously undesirable data out, then I would
have to be more specific & elaborate.

Cheers & thank you ,
Joel