Search fields containing string of given length
Let’s say you have a products table with following structure:
Table Name: Product
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:
SELECT product_code FROM product ORDER BY product_code ASC
The above query will return the result:
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:
That’s all for now.
Share some of your interesting MySQL tips here.