[SOLVED] SQL find and replace

[ober]

I don't think this is possible, but I figured I'd ask you SQL gurus.

 

I have a client that has a bunch of product information in a mysql database and a lot of the descriptions use unordered lists for display.  Unfortunately he forgot to wrap all the < li > tags in < ul > tags.  Without writing a PHP script to loop through all that and update them, is there an easy way to insert the < ul > tags in the right place?

[PFMaBiSmAd]

Probably, but if you could post an example of what the data looks like it would help.

 

If you just wanted to wrap the contents of a column in <ul> </ul> tags -

 

UPDATE your_table SET your_column = CONCAT('<ul>',your_column,'</ul>') WHERE your_where_condition

Your_where_condition in the above would need to use a LIKE '%<li>%' statement to find only entries with a <li>.

 

If the problem is more complex, with text surrounding the <li> </li>, then you would need to use the LOCATE() function to find the positions of the <li> </li> tags, and then use SUBSTRING()/CONCAT() or other string functions to build the result to put back into the column.

[fenway]

Maybe... is there only one "list" per field? If so, you can search for the "last" closing tag and the first opening tag rather easily.

[ober]

Sorry to take so long to get back to this.

 

The field is just the list (it is product specifications).  So I've fixed a handful while I was working on random parts of the website.  I know it would probably make more sense to normalize and dump each spec into a 2 column table and combine it when it's pulled, but I'm taking this over from a previous developer so it would create a fair amount of re-work that I'm not ready to tackle at the moment.

 

So the data falls into 4 categories:

1) a listing with ul tags already applied

2) a listing without ul tags applied

3) blank or null

4) straight text without a list

 

So I would need to do the following:

 

1) Search to see if the ul tags exist, if so, skip

2) Search to see if the ul tags exist, if not, place a ul tag at the start and end

3) Ignore blank/null entries

4) Ignore entries that do not have li tags

 

So... possible?

 

 

[ober]

And I also realize that I probably could have written a PHP script to do it in the time that it took me to write that post... but I want to use the power of the DB for once.

[PFMaBiSmAd]

Give this a try (tested based on your statement of what the four different types of expected data is) -

$query = "UPDATE your_table SET your_column = CONCAT('<ul>',your_column,'</ul>')
WHERE your_column LIKE '%<li>%' AND your_column NOT LIKE '%<ul>%'";

[ober]

Perfect.  Works like a charm.  Thanks!