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.

(Visited 1 times, 1 visits today)
Tags: , ,
Category: SUSE Linux Enterprise Server, Technical Solutions
This entry was posted Wednesday, 29 April, 2009 at 3:23 am
You can follow any responses to this entry via RSS.

Leave a Reply

Your email address will not be published. Required fields are marked *

No comments yet