MySQL group by x characters on an id field

I have a client database that is in a pretty poor state, but I have to work with it. We have a set of product id's and each id is made up of the attributes of the product. What i'd like to do is select those id's that match the first 4 or 5 characters and have them in a drop down box as variants of a selected product.

I've tried 'group by subtr(0,6)', but this seems to return only the first match in开发者_运维知识库 the group. What I need is to have separate queries, that ONLY return ALL the individual rows that have a matching first x characters.

So, for example, I want to have queries that will return: 12345xx 12345yy 12345zz

and then another that will return: 56789xx 56789yy 56789zz

This following query would return only:

12345xx

and then

56789yy for example

select tbl_item.Description, tbl_item.`Description 2`, tbl_item.`Inventory Posting Group`, tbl_item.No_, tbl_item.`Web Description`, tbl_item.`Web Headers`, tbl_item.`Unit Price`, tbl_item.`Sales (Qty_)`, tbl_item.`Product Group Code`
from tbl_item
where tbl_item.`Product Group Code` = ':shopprodid' and tbl_item.`Vendor No_` = '$vendor_no'
group by substr(0,6)

The solution is to put the substr and LIKE in the WHERE clause (no need to group anything). And btw, in MySql the substring starts at index 1, not 0.

select *
from tbl_item
where substr(tbl_item.`Product Group Code`, 1, 6) LIKE ':shopprodid%'

What do you mean by substr(0,6)? the correct syntax is

SUBSTR( str, pos, len )

I suppose you mean:

SUBSTR( tbl_item.id, 0, 6 )

correct that.

And for the results you want you don't need the substr at all, use a query like this:

SELECT * from tbl_item WHERE tbl_item.id LIKE '12345%'

Try something like the following;

SELECT * FROM tbl_item WHERE SUBSTR([<Column name>], 1, 6) = '<Your ID prefix>' ORDER BY [<Column name>]