A doozy of an SQL help request

[hendrickx]

I have listed about 2000 products. Most of them are listed with a title of "This is the thing (Stock code)"

 

I want to run an SQL Query to pull the stuff between ( and ) and put it in the model number field. This is so I can use easyPopulate.

 

Here's the catch: Some items are like "Bracelet (9") (Stock Code)" and some are "Really, really, really long title (Sto" (where OSC didn't let me enter as many characters as I might have liked).

 

Anyone up to posting an SQL query to try? I have a snadbox to play in, I'm not runnning anything on the live database until I can prove it works on the "practice" site -- so hosing my DB is not going to be a concern. Hell, I've hosed it 50 times in the last 2 hours trying this myself, but I didn't know ANY SQL befor I started. :)

 

I do have a working excel formula for use with the easypopulate export. That's:

 

=LEFT(RIGHT(C3, LEN(C3)-IF(ISERROR(FIND("(",C3,(FIND("(",C3)+1))),FIND("(",C3),FIND("(",C3,FIND("(",C3)+1))), LEN(RIGHT(C3, LEN(C3)-IF(ISERROR(FIND("(",C3,(FIND("(",C3)+1))),FIND("(",C3),FIND("(",C3,FIND("(",C3)+1))))-1)

 

If that gives a good starting point. I'd run it on all the items and upload the easypopulate database, but then I get duplicated items.

 

Is there a way to clean out the database an start from fresh? I hate to do that, but if I started with a clean db, I could upload easypopulate and not have to mess with learning enough MySQL to do this.

 

Thanks for the help!

[♥Vger]

Is there a way to clean out the database an start from fresh?

 

Not recommended, but if you want to do that then (just in case) backup the existing database, then delete it using phpMyAdmin, install a new blank table of the same name, and then import the basic installation version of the mysql tables and fields (file oscommerce.sql in catalog/install folder).

 

Vger

[hendrickx]

All right, then anyone got a clue how to write the sql query?

 

Not recommended, but if you want to do that then (just in case) backup the existing database, then delete it using phpMyAdmin, install a new blank table of the same name, and then import the basic installation version of the mysql tables and fields (file oscommerce.sql in catalog/install folder).

 

Vger

<{POST_SNAPBACK}>

[smiley]

All right, then anyone got a clue how to write the sql query?

<{POST_SNAPBACK}>

 

In phpMyadmin you can back up the database & then delete or drop the database. Recommended.

If you have a cpanel you can drop the database there - in the mySql database area.

 

If you need the SQL query:

DROP {DATABASE | SCHEMA} [iF EXISTS] db_name

 

More info here:

http://dev.mysql.com/doc/mysql/en/DROP_DATABASE.html