Mega Code Archive
Repetition Operators
<![CDATA[
Repeat a particular pattern.
Select only addresses that contain vowels we can use this statement:
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value VARCHAR(50)
4 );
Table created.
SQL>
SQL> insert into myTable(id, value)values(1,'1234 4th St. Vancouver');
1 row created.
SQL> insert into myTable(id, value)values(2,'4 Maple Ct. New York');
1 row created.
SQL> insert into myTable(id, value)values(3,'4321 Green Blvd. London');
1 row created.
SQL> insert into myTable(id, value)values(4,'33 Third St. Toronto');
1 row created.
SQL> insert into myTable(id, value)values(5,'One First Drive. Queen');
1 row created.
SQL> insert into myTable(id, value)values(6,'1664 1/2 Springhill Ave');
1 row created.
SQL> insert into myTable(id, value)values(7,'665 Fall Ave. Linken');
1 row created.
SQL>
SQL> select * from mytable;
ID VALUE
---------- --------------------------------------------------
1 1234 4th St. Vancouver
2 4 Maple Ct. New York
3 4321 Green Blvd. London
4 33 Third St. Toronto
5 One First Drive. Queen
6 1664 1/2 Springhill Ave
7 665 Fall Ave. Linken
7 rows selected.
SQL>
SQL> SELECT value, REGEXP_INSTR(value,'[aeiou]')
2 where_pattern_starts
3 FROM myTable
4 WHERE REGEXP_INSTR(value,'[aeiou]') > 0;
VALUE WHERE_PATTERN_STARTS
-------------------------------------------------- --------------------
1234 4th St. Vancouver 15
4 Maple Ct. New York 4
4321 Green Blvd. London 8
33 Third St. Toronto 6
One First Drive. Queen 3
1664 1/2 Springhill Ave 13
665 Fall Ave. Linken 6
7 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
]]>