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

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
3 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
AnoopdevilBillRobert Recent comment authors
newest oldest most voted
Robert
Guest
Robert

It work to me,think you!

devil
Guest
devil

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;

Anoop
Guest
Anoop

select ‘abc_[0-9].csv’ ~ ‘abc_0.csv’ returns false. What changes can I do to make it true.