Re: regexp_matches for digit
| От | Steve Crawford |
|---|---|
| Тема | Re: regexp_matches for digit |
| Дата | |
| Msg-id | 559EA9BD.9030502@pinpointresearch.com обсуждение исходный текст |
| Ответ на | regexp_matches for digit (Ramesh T <rameshparnanditech@gmail.com>) |
| Список | 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
The tilde operator works fine for me.
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}$'
?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
В списке pgsql-general по дате отправления: