Article
2059
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.
Disclaimer: As with everything else at Cool Solutions, this content is definitely not supported by Novell (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.
Related Articles
User Comments
- Be the first to comment! To leave a comment you need to Login or Register
- 2059 reads
0