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:
Post a Comment