[SOLVED] Clean up field in my SQL useing Regex to extract part of a url

[JohnDeer]

I have a column in a MYSQL database that  I need to try and clean up and I was told that maybe to use PHP would be the best bet.

 

Here is a copy of the field there are about 900 records like this in the database and I want to try and remove all of the HTML apart from the category name

 

<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> 

 

In this case the category name is home-diy every thing else can go :-) any help on this would be handy as I have never used php before.

[effigy]

All of the content you posted should be replaced with "home-diy", correct? What are some examples of other records--do they all have the same url format?

[JohnDeer]

Hi effigy thank you fro the reply you are correct about the "home-diy" as the first category there are about 10 categories in total and they layout is the same here are a few more examples

 

category "shopping"

 

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

 

category "pets"

 

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

 

category "money"

 

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

[effigy]

This can be done easily by creating a custom function. What version of MySQL are you using?

[JohnDeer]

MYSQL 4.1

 

A friend said I should be able to do this direct in SQL

 

select SUBSTRING(oldCATEGORY,PATINDEX('%gory/%',oldCATEGORY)+5,(PATINDEX('%/">%',oldCATEGORY)) - (PATINDEX('%gory/%',oldCATEGORY)+5)) 

,oldCATEGORY

from database

where oldCategory <> ''

 

But looking at this it looks like it is for MSSQL

[effigy]

True, but the same logic applies:

SELECT SUBSTRING(field, INSTR(field, 'gory/')+5, (INSTR(field, '/">')-(INSTR(field, 'gory/')+5))) AS category FROM table

I'm moving this to MySQL since it's not utilizing regular expressions.

[JohnDeer]

Thank you effigy :-) I was going to use a regular expression to clean it up in PHP as the SQL guys said it would be easy to do some thing like this in php but it looks like you proved them wrong and its just as easy to do it in SQL thak you for your time and help :-)