regexp_matches for digit

Rames,

Hope this will be useful

http://www.postgresql.org/docs/9.1/static/functions-string.html

Jimit Amin

On Thu, Jul 9, 2015 at 9:54 PM, Ramesh T <rameshparnanditech@gmail.com>
wrote:

On 7/9/2015 11:24 AM, Ramesh T wrote:

Hi,
in oracle regexp_like(entered
date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')

for postgres i have regexp_matches ,But i need how to match [:digit:] in
postgres when we pass date..?
any help

\d

per:
http://www.postgresql.org/docs/devel/static/functions-matching.html

# select 1 where '1234-56-78' ~ '\d{4}-\d{2}-\d{2}';
?column?
----------
1
(1 row)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
in oracle regexp_like(entered
date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')

for postgres i have regexp_matches ,But i need how to match [:digit:] in
postgres when we pass date..?
any help

[:digit:] is Posix syntax, supported by Postgres.

Looks good to me:

graal=# select regexp_matches('2015-07-09',
'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
regexp_matches
----------------
{2015-07-09}
(1 row)

graal=# select regexp_matches('2015-x7-09',
'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
regexp_matches
----------------
(0 rows)

What do you need, exactly?

Bye,
Chris.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/09/2015 09:24 AM, Ramesh T wrote:

Hi,
in oracle regexp_like(entered
date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')

for postgres i have regexp_matches ,But i need how to match [:digit:]
in postgres when we pass date..?
any help

Konsole output

The tilde operator works fine for me.

Konsole output
select '2014-05-05' ~ '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}';
?column?
----------
t

But if you are attempting to validate a date the regex is *way* too
simplistic as it will match any manner of junk:
123456-78-901234
thisisan0000-00-00invaliddate
etc.

At a minimum you need to anchor the ends with ^ and $:
'^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}$'

If you can make reasonable assumptions about date ranges you can catch
more errors with something like:
'^20[[:digit:]]{2}-[01][[:digit:]]{1}-[0123][[:digit:]]{1}$'

But trying to truly validate dates purely with a regex is more effort
than I'm willing to put in. I don't recall where I ran across this
snippet but it creates a function that ensures that the date is
acceptable to PostgreSQL without raising an error:

CREATE OR REPLACE FUNCTION is_valid_date(text)
RETURNS bool AS
'
begin
return case when $1::date is null then false else true end;
exception when others then
return false;
end;
'
LANGUAGE 'plpgsql' VOLATILE;

Cheers,
Steve

nice i'm looking for this,i thought digit don't work in postgres..
thanks

On Thu, Jul 9, 2015 at 10:21 PM, Chris Mair <chris@1006.org> wrote: