Thread: enforcing alphabetic/numeric content in "char" column
Hello, The task is to allow data entries like "007", leading zeros included. I tried various numeric data types and they all trim it down to "7". Using char(3) works, but I want to put a CHECK on it to ensure no non-numeric data is entered. The following, and numerous variations on it, do not work: iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]') Any suggestions ? Cheers, Joel
> -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Joel Rodrigues > Sent: Thursday, November 07, 2002 9:07 AM > To: pgsql-novice@postgresql.org > Subject: [NOVICE] enforcing alphabetic/numeric content in "char" column > > > Hello, > > The task is to allow data entries like "007", leading zeros > included. I tried various numeric data types and they all trim > it down to "7". Using char(3) works, but I want to put a CHECK > on it to ensure no non-numeric data is entered. The following, > and numerous variations on it, do not work: > > iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]') > > Any suggestions ? > Your regex should be '^[0-9]$' (the one you have will allow any string as long as there is a numeric character in it somewhere).
On Thu, Nov 07, 2002 at 19:37:23 +0530, Joel Rodrigues <borgempath@Phreaker.net> wrote: > > iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]') iso3dnum char(3) CHECK (iso3dnum ~ '^[0-9][0-9][0-9]$')
Rod Kreisler <rod@23net.net> writes:
> Your regex should be '^[0-9]$' (the one you have will allow any string as
> long as there is a numeric character in it somewhere).
Almost: that only allows a single digit. Probably '^[0-9]+$' is the
correct regex.
regards, tom lane
Rod Kreisler" <rod@23net.net> wrote:
> Joel Rodrigues wrote:
> > Hello,
> >
> > The task is to allow data entries like "007", leading zeros
> > included. I tried various numeric data types and they all trim
> > it down to "7". Using char(3) works, but I want to put a CHECK
> > on it to ensure no non-numeric data is entered. The following,
> > and numerous variations on it, do not work:
> >
> > iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]')
> >
> > Any suggestions ?
> >
>
> Your regex should be '^[0-9]$' (the one you have will allow any string as
> long as there is a numeric character in it somewhere).
'^[0-9]$' only allows exactly one digit
'^[0-9]+$' allows one or more digis
'^[0-9]{3,3}$' means exactly 3 digits
Regards,
Michael Paesold
On Thursday, November 7, 2002, at 10:31 , Bruno Wolff III wrote: > On Thu, Nov 07, 2002 at 19:37:23 +0530, > Joel Rodrigues <borgempath@Phreaker.net> wrote: >> >> iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]') > > iso3dnum char(3) CHECK (iso3dnum ~ '^[0-9][0-9][0-9]$') > Thank you Bruno & Rod ! This one works. So it's one [0-9] for every character. Obrigado ! Joel
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Joel Rodrigues
> Sent: Friday, November 08, 2002 7:11 AM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] enforcing alphabetic/numeric content in "char"
> column
>
>
> On Thursday, November 7, 2002, at 10:31 , Bruno Wolff III wrote:
>
> > On Thu, Nov 07, 2002 at 19:37:23 +0530,
> > Joel Rodrigues <borgempath@Phreaker.net> wrote:
> >>
> >> iso3dnum char(3) CHECK (iso3dnum ~ '[0-9]')
> >
> > iso3dnum char(3) CHECK (iso3dnum ~ '^[0-9][0-9][0-9]$')
> >
>
> Thank you Bruno & Rod ! This one works. So it's one [0-9] for
> every character.
>
> Obrigado !
> Joel
>
My original, as was pointed out, missed something important. I had written
'^[0-9]$' which would validate one character. Bruno's suggestion will work
as will '^[0-9]{3}$'. The important thing to pick up on is the leading '^'
and trailing '$' which indicate the start and end of the string
respectively. Without them, any other character can exist in the string IN
ADDITION TO the pattern specified.
IOW, '^[0-9]{3}$' literally means a 3 digit string and nothing else (i.e.
000 - 999). The pattern '[0-9]{3}' means any string that contains a 3
digit sequence. e.g. 'ab345kdjflkasdjf' would be valid.
HTH