Checking for a number

-----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 entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

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

--- 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}$';

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