Pair Down List

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Pair Down List

Post by WanamakerStudios »

I have a result set as follows:

CHEROKEE
CHEROKEE 4.0 L
CHEROKEE 4X4
CHEROKEE 4X4 LAREDO
CHEROKEE 4X4 LIMITED
CHEROKEE 4x4 SPORT
CHEROKEE 4X4SPORT
CHEROKEE CLASSIC
CHEROKEE CLASSIC 4WD
CHEROKEE CLASSIC 4X4
CHEROKEE CLASSIC CLASSIC
CHEROKEE CLASSIC LIMITED
CHEROKEE CLASSIC SPORT
CHEROKEE COUNTRY
CHEROKEE LARADEO
CHEROKEE LARED0
CHEROKEE LAREDO
CHEROKEE LIMITED
CHEROKEE LIMITED 4WD
CHEROKEE LIMITED 4X4
CHEROKEE LTD
CHEROKEE PIONEER
CHEROKEE SE
CHEROKEE SE 4X4
CHEROKEE SPORT
CHEROKEE SPORT 4 DR
CHEROKEE SPORT 4WD
CHEROKEE SPORT 4x4
CHEROKEE SPORT 60 YRS ED
CHEROKEE SPORT LIFTED
CHEROKEE SPORT V6
CJ5
CJ7
CJ7 4X4
CJ7 PLOW
COMANCHE
COMANCHE 4X4
COMANCHE PIONEER 4WD
COMMANDER
COMMANDER 2WD
COMMANDER 4wd
COMMANDER 4X4

How can I write my SELECT statement so that only CHEROKEE, CJ5, CJ7, COMANCHE and COMMANDER show up on my list? I'm doing this for searchablity so I can run searchs on LIKE %CHEROKEE% and have it return all of the above
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SUBSTRING_INDEX() or your database's equivalent.

Honestly, myself, I would probably break it down further. For example, the main-model names could be in a table. The secondary model names could be in another table and then you'd have the trim names in yet another table. The package would link all three together in some fashion.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Ok ... I figured the SUBSTRING_INDEX() part out ... but how do I get around this problem ...

I currently have the SELECT statement as follows:

Code: Select all

SELECT SUBSTRING_INDEX(`CMODEL`, ' ', 1) AS CMODEL FROM `vehicles-temp`
WHERE CMAKE = 'Au'
GROUP BY CMODEL
However, I have some CMODEL names that have a space in their proper name (ie: Grand Cherokee). How do I include them?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

As I suggested before, breaking the names apart is likely the best solution in the end.
Post Reply