POSTCODE REGEXP '[0-9][0-9]*[a-z][a-z]*' : REGEXP « Regular Expression « SQL / MySQL






  1. SQL / MySQL
  2. Regular Expression
  3. REGEXP

POSTCODE REGEXP '[0-9][0-9]*[a-z][a-z]*'

      
mysql>
mysql>
mysql> CREATE TABLE EmployeeS(
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          NAME           CHAR(15)     NOT NULL,
    ->          INITIALS       CHAR(3)      NOT NULL,
    ->          BIRTH_DATE     DATE                 ,
    ->          SEX            CHAR(1)      NOT NULL,
    ->          JOINED         SMALLINT     NOT NULL,
    ->          STREET         VARCHAR(30)  NOT NULL,
    ->          HOUSENO        CHAR(4)              ,
    ->          POSTCODE       CHAR(6)              ,
    ->          TOWN           VARCHAR(30)  NOT NULL,
    ->          PHONENO        CHAR(13)             ,
    ->          LEAGUENO       CHAR(4)              ,
    ->          PRIMARY KEY    (EmployeeNO)           );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (2, 'Jack', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (6, 'Link', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'First Way','39', '9758VB', 'Stratford', '070-347689', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (8, 'Mary', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long DRay','804', '8457DK', 'Eltham', '079-234857', '2513');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'First Way','16', '4377CB', 'Stratford', '070-473458', '6409');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Main Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (100, 'Link', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (104, 'Jane', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT   EmployeeNO, POSTCODE
    -> FROM     EmployeeS
    -> WHERE    POSTCODE REGEXP '[0-9][0-9]*[a-z][a-z]*';
+------------+----------+
| EmployeeNO | POSTCODE |
+------------+----------+
|          2 | 3575NH   |
|          6 | 1234KK   |
|          7 | 9758VB   |
|          8 | 6584WO   |
|         27 | 8457DK   |
|         28 | 1294QK   |
|         39 | 9629CD   |
|         44 | 4444LJ   |
|         57 | 4377CB   |
|         83 | 1812UP   |
|         95 | 5746OP   |
|        100 | 6494SG   |
|        104 | 9437AO   |
|        112 | 6392LK   |
+------------+----------+
14 rows in set (0.00 sec)

mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)

mysql>








Related examples in the same category

1.REGEXP 'e'
2.REGEXP '^ba'
3.REGEXP and CONCAT
4.REGEXP '[abc]'
5.REGEXP 'm.n'
6.REGEXP '[men][men]'
7.NAME REGEXP '^[a-z]{7}'
8.NAME REGEXP '^[a-z]{6,7}$'
9.POSTCODE REGEXP '4{4}'
10.REGEXP '[[.space.]]'
11.REGEXP '[[:<:]]Street[[:>:]]'
12.NAME REGEXP '^n.*e$'
13.REGEXP '[a-z]{9}'
14.Regular expressions do not match NULL values. This is true both for REGEXP and for NOT REGEXP
15.SELECT c, c REGEXP '.', c REGEXP '^', c REGEXP '$' FROM mytable;
16.With REGEXP, you need a double backslash to match a metacharacter literally:
17.The primary options that you can use with the REGEXP operator to create expressions in your SQL statements.
18.Be more specific with the REGEXP operator by extending the specified value used by the operator
19.Pattern Matching with REGEXP
20.The command REGEXP offers many more possibilities for formulating a pattern,