Tuesday, August 2, 2011

REGEXP_LIKE examples

A very simple example explaining a few functionalities using  REGEXP_LIKE operator. Much more variations/functionality can be achieved using different-2 search conditions. 

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Aug 2 15:49:46 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn scott@test123
Enter password:
ERROR:
ORA-28000: the account is locked


SQL>
SQL>
SQL> conn scott@orcl105
Enter password:
Connected.
SQL>
SQL>
SQL> drop table ta_regexp_like;
drop table ta_regexp_like
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ta_regexp_like
2 (
3 contact_info varchar2(100)
4 )
5 /

Table created.

SQL>
SQL>
SQL> insert into ta_regexp_like values('Contact number for smith is 238-564-7645');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Adam is 22-269-45');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Sumit is 64-75');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Rajeev is 4564-564-7');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Rajeev is sdas-767-9');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL> select contact_info
2 from ta_regexp_like
3 where regexp_like(contact_info,'..-...')
4 /

CONTACT_INFO
--------------------------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7
Contact number for Rajeev is sdas-767-9

SQL>
SQL>
SQL>

Query not to select sdas-564-7 and select only those rows having
just digits on both sides of '-'

SQL>
SQL>
SQL> select contact_info
2 from ta_regexp_like
3 where regexp_like(contact_info,'[0-9]{2}-[0-9]{3}')
4 /

CONTACT_INFO
--------------------------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7

SQL>
SQL>
SQL>


No comments: