SUSE Conversations


MySQL Tips



By: mendesdomnic

April 29, 2009 3:23 am

Reads:207

Comments:0

Rating:0

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.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Tags: , ,
Categories: SUSE Linux Enterprise Server, Technical Solutions

Disclaimer: As with everything else at SUSE Conversations, this content is definitely not supported by SUSE (so don't even think of calling Support if you try something and it blows up).  It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.

Comment

RSS