MySQL Query and REGEXP

[monkey64]

I have a recordset and a field [description] conataining these values:

Levis 501 Jeans - Blue
Levis 501 Jeans - Red
Levis 501 Jeans - Green
Panasonic TZ7 - Grey
Panasonic TZ7 - Silver
Panasonic TZ7 - Black

I have 3 other fields I must include in the query.

I want to strip off everything after the dash, ending up with just "Levis 501 Jeans" or "Panasonic TZ7"
I also don't want any duplicates in this field; I want to end up with just two records.

Is it possible to do all this in one SQL Query, using REGEXP or do I have to create a PHP array and check for duplicates and use something like this to strip off everything after the dash?:

if(stripos($row['description'], '-') == true) {
$desc = substr($row['description'], 0, strpos($row['description'],'-'));
}

How would you do it?

 

[HarleyQuinn]

I wouldn't use a REGEXP at all, I'd use TRIM and SUBSTRING_INDEX, something like:

SELECT TRIM(TRAILING SUBSTRING_INDEX('Levis 501 Jeans - Blue'," ",-2) FROM 'Levis 501 Jeans - Blue');

Please note this is untested as I don't use MySQL as work.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[HarleyQuinn]

Oops, forgot to address your other question. To get the two rows you can use DISTINCT but that will only return the two rows you require if the other fields you are adding are the same for all of the Levi's records (and all the Panasonic one's are the same too). Otherwise you will return multiple rows.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[monkey64]

Harley

God I'm amazed what you can do with MySQl! Your post pushed me in the right direction and I have now got a working model.

Thanks again.

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post