MY SQL Clean up a field

SimonT · 03-30-2007, 10:26 AM

Once again I need help with a little MYSQL I have a column that holds some thing like this

Code:

<A href="http://www.mysite.com/blog/category/home-diy/"><IMG class="" height=149 src="http://www.mysite.com/blog/images/home.png" width=225></A>

There are about 900 records and I want to just extract the category and enter it in to the next column. The category changes every now and again there are about 20 in total is there a easy way to do this ?

jlinkels · 03-30-2007, 05:12 PM

I think it is easy what you ask, but you'd need to post a better description of you problem. For example the related tables and table layout that you are using.

The link that you posted tells absolutly nothing about your SQL problem.

jlinkels

SimonT · 03-30-2007, 06:51 PM

Hi jlinkels thank you for the reply this database is for a legacy blog application that I want to try and convert to wordpress. Before I try and do any converting I need to try and get the basic fields sorted out, as this blog application did not hold a field with the category. I created the CATEGORY my self and now need to some how extract the "category word" from the SUMMARY field and insert it in to the CATEGORY field

Here is a copy of the database all fields are populated but not the CATEGORY field as I created that. The html code that I pasted to you in the first post is what is held in the SUMMARY field. I need to try and extract that and pull out the "category word" and insert it in to the CATEGORY field.

The first example I posted would need to try and extract "category word" home-diy.

I have a list of all the possible categories if that would helps

Code:

CREATE TABLE `BLOG` (
`SUMMARY` varchar(100) default NULL,
`CATEGORY` varchar(20) default NULL,
`TITLE` varchar(110) default NULL,
`CONTENT` text,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

jlinkels · 03-31-2007, 07:30 AM

Well, that is a little bit less easy, I thought that you wanted just to update a column. I have not completely succeeded either, but maybe I have found something useful.

First I created a table which contains all categories. I called that 'categories'. It has only one column: 'category'.

Then I created another table which I called 'export', with two colums, 'summary' and 'category'. You might want to add some more columns yourself like 'title' etc.

Now to populate the 'exports' table, I combined all rows in 'blog' with one row in 'categories' using the condition that the word in 'categories' is part of the string in 'blog'.

Code:

INSERT into EXPORT SELECT blog.summary,categories.category FROM blog
 INNER JOIN categories ON summary REGEXP categories.category;

Now the contects of the blog table is:

Code:

mysql> select * from blog;
+-------------------------------------------------------+----------+
| summary                                               | category |
+-------------------------------------------------------+----------+
| This is something which belongs in category technical | NULL     |
| Another something which belongs in category technical | NULL     |
| This is something which belongs in category social    | NULL     |
| Another social event description                      | NULL     |
| technical                                             | NULL     |
+-------------------------------------------------------+----------+
5 rows in set (0.01 sec)

The categories

Code:

mysql> select * from categories;
+-----------+
| category  |
+-----------+
| technical |
| social    |
+-----------+
2 rows in set (0.00 sec)

And finally the combined result in exports:

Code:

mysql> select * from export;
+-------------------------------------------------------+-----------+
| summary                                               | category  |
+-------------------------------------------------------+-----------+
| This is something which belongs in category technical | technical |
| Another something which belongs in category technical | technical |
| technical                                             | technical |
| This is something which belongs in category social    | social    |
| Another social event description                      | social    |
+-------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

Maybe there are smarter ways to perform this, and even write the category column back into the blog table, but this what I could come up with. After all it is just a one time use I assume.

jlinkels

SimonT · 03-31-2007, 08:36 PM

jlinkels you are a genius thank you for this I will give it a shot my only other idea was to maybe clean up the field with a Regular expression any thing from the left of "mysite.com/blog/" remove any thing from the right of "/"><IMG class=" remove.

Of cause I would first have to make a copy of the column