Search fields containing string of given length
Let’s say you have a products table with following structure:
Table Name: Product
| Field | Type |
|---|---|
| id | int(11) |
| product_code | varchar(100) |
| brand_id | int(11) |
| price | decimal(10,4) |
| tax_type_id | int(11) |
| quantity | int(11) |
| weight | decimal(10,3) |
| cube | decimal(10,3) |
| has_options | tinyint(4) |
| is_active | tinyint(4) |
| is_featured | tinyint(4) |
| is_deleted | tinyint(4) |
| created_at | datetime |
| updated_at | datetime |
Now you want to find all products having a product code that contains exactly five characters. This can done using the special character “_”(underscore).
The following will display all product details having 5 character product codes.
select * from product where product_code like '_____';
Storing numbers in the varchar field? Here is how you can sort them.
Continuing with the same table structure given above.
Assuming that product_code contains numbers, say you need to select the product details and order them by product_code.
Let’s say following is the data in the field product_code:
product_code
345
30
450
1000
SELECT product_code FROM product ORDER BY product_code ASC
The above query will return the result:
product_code
1000
30
345
450
The above result is undesired as it does not order the varchar field numerically.
The same can be achieved using the following query:
SELECT product_code FROM product ORDER BY product_code + 0 ASC
[note here the +0 forces a numeric sort]
This will return the correct result as below:
product_code
30
345
450
1000
That’s all for now.
Share some of your interesting MySQL tips here.
No comments yet