Be more specific with the REGEXP operator by extending the specified value used by the operator : REGEXP « Regular Expression « SQL / MySQL






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

Be more specific with the REGEXP operator by extending the specified value used by the operator

       
mysql>
mysql>
mysql> CREATE TABLE CDs
    -> (
    ->     CDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     OnOrder SMALLINT UNSIGNED NOT NULL,
    ->     Reserved SMALLINT UNSIGNED NOT NULL,
    ->     Department ENUM('Classical', 'Popular') NOT NULL,
    ->     Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO CDs (CDName, InStock, OnOrder, Reserved, Department, Category) VALUES
    -> ('Xml', 10, 5, 3, 'Popular', 'Rock'),
    -> ('Java', 10, 5, 3, 'Classical', 'Opera'),
    -> ('SQL', 17, 4, 1, 'Popular', 'Jazz'),
    -> ('MySQL', 9, 4, 2, 'Classical', 'Dance'),
    -> ('CSS', 24, 2, 5, 'Classical', NULL),
    -> ('HTML', 16, 6, 8, 'Classical', NULL),
    -> ('Oracle', 2, 25, 6, 'Popular', 'Blues'),
    -> ('Javascript', 32, 3, 10, 'Popular', NULL),
    -> ('Data type', 12, 15, 13, 'Popular', 'Country'),
    -> ('Flash', 5, 20, 10, 'Popular', 'New Age'),
    -> ('Ajax', 24, 11, 14, 'Popular', 'New Age'),
    -> ('Photoshop', 42, 17, 17, 'Classical', NULL),
    -> ('Word', 25, 44, 28, 'Classical', 'Dance'),
    -> ('iPhone', 32, 15, 12, 'Classical', 'General'),
    -> ('MacBook', 20, 10, 5, 'Classical', 'Opera'),
    -> ('Linux', 23, 12, 8, 'Classical', 'General'),
    -> ('Shell', 23, 10, 17, 'Popular', 'Country'),
    -> ('Pascal', 18, 20, 10, 'Popular', 'Jazz'),
    -> ('Ruby', 22, 5, 7, 'Popular', 'Blues'),
    -> ('Sql Server', 28, 17, 16, 'Classical', 'General'),
    -> ('Opera', 10, 35, 12, 'Classical', 'Opera'),
    -> ('Safari', 15, 30, 14, 'Popular', NULL),
    -> ('C', 42, 0, 8, 'Popular', 'Blues'),
    -> ('C++', 16, 8, 8, 'Classical', 'General');
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT CDName, InStock
    -> FROM CDs
    -> WHERE CDName REGEXP '^[mn].*[sz]$'
    -> ORDER BY CDName;
Empty set (0.00 sec)

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








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