Use _ to match a phone number : LIKE « Query Select « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> CREATE TABLE employees (
  2    au_id    CHAR(3)     NOT NULL,
  3    au_fname VARCHAR(15) NOT NULL,
  4    au_lname VARCHAR(15) NOT NULL,
  5    phone    VARCHAR(12) NULL    ,
  6    address  VARCHAR(20) NULL    ,
  7    city     VARCHAR(15) NULL    ,
  8    state    CHAR(2)     NULL    ,
  9    zip      CHAR(5)     NULL
 10  );

Table created.

SQL>
SQL> INSERT INTO employees VALUES('A01','S','B','111-111-1111','75 St','Boston','NY','11111');

1 row created.

SQL> INSERT INTO employees VALUES('A02','W','H','222-222-2222','2922 Rd','Boston','CO','22222');

1 row created.

SQL> INSERT INTO employees VALUES('A03','H','H','333-333-3333','3800 Ave, #14F','San Francisco','CA','33333');

1 row created.

SQL> INSERT INTO employees VALUES('A04','K','H','444-444-4444','3800 Ave, #14F','San Francisco','CA','44444');

1 row created.

SQL> INSERT INTO employees VALUES('A05','C','K','555-555-5555','114 St','New York','NY','55555');

1 row created.

SQL> INSERT INTO employees VALUES('A06',' ','K','666-666-666','390 Mall','Palo Alto','CA','66666');

1 row created.

SQL> INSERT INTO employees VALUES('A07','P','O','777-777-7777','1442 St','Sarasota','FL','77777');

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT au_fname, au_lname, phone
  2    FROM employees
  3    WHERE phone NOT LIKE '212-___-____'
  4      AND phone NOT LIKE '415-___-%'
  5      AND phone NOT LIKE '303-%';

AU_FNAME        AU_LNAME        PHONE
--------------- --------------- ------------
S               B               111-111-1111
W               H               222-222-2222
H               H               333-333-3333
K               H               444-444-4444
C               K               555-555-5555
                K               666-666-666
P               O               777-777-7777

7 rows selected.

SQL>
SQL> drop table employees;

Table dropped.

SQL>








2.19.LIKE
2.19.1.Using the LIKE Operator
2.19.2.Underscore character (_) matches one character in a specified position.
2.19.3.Percent character (%) matches any number of characters beginning at the specified position.
2.19.4.ESCAPE option
2.19.5.Combine UPPER and LIKE operator
2.19.6.Subquery with Like operator
2.19.7.Use _ and % together
2.19.8.Use three _ together
2.19.9.Use _ to match a phone number
2.19.10.Second letter is A
2.19.11.Using Pattern Matching LIKE '____ %'
2.19.12.ESCAPE from LIKE