MySQL Tips

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:

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.