Postgres Pro
Downloads
Home   >   mailing lists

Thread: Checking for a number

Checking for a number

From
Warren
Date:
I need to check if the last two characters of a field are a number. I am
trying something like this but it does not want to work.

substring(TRIM(field8) from '..$') SIMILAR TO '\d\d'

How should I do this?

--
Thanks,

Warren Bell

Re: Checking for a number

From
Lee Keel
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Warren
> Sent: Wednesday, June 27, 2007 12:31 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Checking for a number
>
> I need to check if the last two characters of a field are a number. I am
> trying something like this but it does not want to work.
>
> substring(TRIM(field8) from '..$') SIMILAR TO '\d\d'
>
> How should I do this?
>
> --
> Thanks,
>
> Warren Bell
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Warren,

Try this...

select substring(trim(both ' ' from field8) from E'\\d{2}$') from TABLENAME
where texticregexeq(trim(both ' ' from field8), E'\\d{2}$')

This will limit the query to only the rows that end in 2 digits as well as
return those digits for you.

Hope that helps,
Lee Keel

This email and any files transmitted with it are confidential and intended solely for the use of the individual or
entityto whom they are addressed. If you have received this email in error please notify the sender. This message
containsconfidential information and is intended only for the individual named. If you are not the named addressee you
shouldnot disseminate, distribute or copy this e-mail.

Re: Checking for a number

From
Osvaldo Rosario Kussama
Date:
Warren escreveu:
> I need to check if the last two characters of a field are a number. I am
> trying something like this but it does not want to work.
>
> substring(TRIM(field8) from '..$') SIMILAR TO '\d\d'
>
> How should I do this?
>

Try:
SELECT your_field ~ '.*[[:digit:]]{2}$';

Osvaldo

Re: Checking for a number

From
Richard Broersma Jr
Date:
--- Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br> wrote:
> Try:
> SELECT your_field ~ '.*[[:digit:]]{2}$';

This could be simplified a little. :o)

WHERE your_field ~ '\\d{2}$';

Re: Checking for a number

From
Michael Glaesemann
Date:
On Jun 27, 2007, at 16:17 , Richard Broersma Jr wrote:

>
> --- Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br> wrote:
>> Try:
>> SELECT your_field ~ '.*[[:digit:]]{2}$';
>
> This could be simplified a little. :o)
>
> WHERE your_field ~ '\\d{2}$';

Using dollar-quotes means not having to escape your \d (which I
always find a bit of a hassle):

WHERE your_field ~ $re$\d{2}$$re$;

It's important to remember to use a tagged dollar quote (e.g., $re$)
if you're using $ as an anchor.

And with standard_conforming_strings on you don't even need to use
dollar-quotes:

test=# show standard_conforming_strings;
standard_conforming_strings
-----------------------------
on
(1 row)

test=# select 'foo33' ~ '\d{2}$';
?column?
----------
t
(1 row)

Dollar-quoting gets you around having to worry about what
standard_conforming_strings is set to, though.

Michael Glaesemann
grzm seespotcode net