Mysql-4 data retrieval (2), mysql-4 Data Retrieval

Mysql-4 data retrieval (2), mysql-4 Data Retrieval
Use wildcards to filter like operator % wildcard % can match any character

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';

The underline wildcard can only match one character.

SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';

Use regular expressions to search for basic character matching

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

Retrieve all rows in which prod_name contains text 1000

 

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

Note:

SELECT prod_name FROM products WHERE prod_name LIKE '000000' order by prod_name;

 

SELECT prod_name FROM products WHERE prod_name REGEXP '000000' order by prod_name;

Why does the first like statement show null? This is because like matches the entire column. If the matched text appears in the column value, like will not find it, the corresponding row will not be returned, unless it is a wildcard, and REGEXP matches the column value. If the matched text appears in the column value, REGEXP will find it, the corresponding row is returned, which is a very important difference.

 

OR matching

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

Matching range: [1-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

SELECT vend_name FROM vendors WHERE vend_name REGEXP '. 'order BY vend_name;. match any character

To match special characters, \ must be used as the leading character, \-Indicates search-

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

Metacharacters	Description
*	0 or multiple matches
+	One or more matches (equal to {1 ,})
?	0 or 1 matching (equal to {0, 1 })
{N}	Number matching
{N ,}	Not less than number matching
{N, m}	The number of matches ranges from n to m and does not exceed 255.

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

Sticks? Match stick or sticks \ (matching parentheses

 

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

Class	Description
[: Alnum:]	Any letter and number (same as [a-zA-Z0-9])
[: Alpha:]	Any character (same as [a-zA-Z])
[: Blank:]	Space and orders table (same as \ t)
[: Cntrl:]	ASCLL control characters,
[: Digit:]	Any number (same as [0-9])
[: Graph:]	Same as print, but does not contain spaces
[: Lower:]	Any lowercase letter ([a-z])
[: Pirnt:]	Any printable characters
[: Upper:]	Any uppercase letter in the same way as the [A-Z]
[: Xdigit:]	Any hexadecimal number (same as [a-fA-F0-9])

 

Operator

Metacharacters	Description
^	Start of Text
&	End of Text
[[: <:]	Start of Word
[[:>:]	End of a word

 

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

All products starting with one digit (including the number of decimal points)

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%';SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';SELECT prod_name FROM products WHERE prod_name LIKE 's%e';SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;