Regular Expression in PostgreSQL

By mkyong | Updated: September 19, 2009

Viewed: 301 (+3 pv/w)

Tags: postgresql postgresql regular expression

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