Regular Expression in PostgreSQL
Regular Expression is a very powerful tools for programming language like java, .NET, PHP , Perl…or even PostgreSQL.
Here i write some basic examples to show how to use regular expression in PostgreSQL.
1) isdigit function – This function is missing in PostgreSQL, as this is a built-in function in others database.
This is a very useful function to validate only number allow. However we can create isdigit function ourself in PostgreSQL.
create or replace function isdigit(text) returns boolean as '
select $1 ~ ''^(-)?[0-9]+$'' as result
' language sql;
--test function
select isdigit('1') --return true
select isdigit('A') --return false
2) isString function – This function is missing in PostgreSQL as well, as this is a built-in function in others database.
This is a very useful function to validate only string allow. However we can create isString function ourself in PostgreSQL.
create or replace function isString(text) returns boolean as '
select $1 ~ ''^(-)?[a-zA-Z]+$'' as result
' language sql;
--test function
select isString('1') --return false
select isString('A') --return true
3) isIPAddress function – I do not think this is a built-in function in other database. This functions is use to validate IP Address allow.
Example A
--check ipaddress
create or replace function isIPAddress(text) returns boolean as '
select $1 ~ ''^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$'' as result
' language sql;
--test function
select isIPAddress('202.111.0.1') --return true
select isIPAddress('202.ZZZ.0.A') --return false
select isIPAddress('202.11199999999.1.100') --return true
Example B
create or replace function isIPAddressStrict(text) returns boolean as '
select $1 ~ ''^[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?$'' as result
' language sql;
--test function
select isIPAddressStrict('202.111.0.1') --return true
select isIPAddressStrict('202.ZZZ.0.A') --return false
select isIPAddressStrict('202.11199999999.1.100') --return false
Wow, impressive right? Actually Regular Expression is more powerful than above simple function. It is worth to invest time to study on it. Please go here if you want to know more about it
http://en.wikipedia.org/wiki/Regular_expression
Please share your example to me also, if you do not mind. Thanks
select ‘abc_[0-9].csv’ ~ ‘abc_0.csv’ returns false. What changes can I do to make it true.
you can also use for question 3:
create or replace function isIPAddress(text) returns boolean as ‘
select $1 ~ ”d{3}.d{3}.d{2}.d{2}” as result
‘ language sql;
It work to me,think you!
Very helpful! There is a slight issue with the code above; the period is not properly escaped, resulting in the expression erroneously matching characters other than ‘.’.
Example:
SELECT isIPAddressStrict(‘202.111.0a1′) –return true
Alternative function:
CREATE OR REPLACE FUNCTION is_ipaddress_strict(text) RETURNS BOOLEAN AS
$BODY$
select $1 ~ E’^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$’ as result;
$BODY$
LANGUAGE sql VOLATILE;
Postgres Regex Escaping explained:
http://www.regular-expressions.info/postgresql.html
Note: Using Postgres 8.2.x (Greenplum 4.2.2)