Jump to content

[SOLVED] SQL find and replace


ober

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.