MySQL Tips
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