4

I have a table with a field that contains descriptions for items sold on my site.

In our infinite wisdom, when we first started the site, we pasted code for a Facebook Like button into the description, each just a little different, as it has product info in the link. We now have about 400 items with the code in it.

This is an example of what I'm dealing with:

-Whipped Up, Fluffy Chocolate-On-Chocolate Taste; A Lighter Way To Enjoy Chocolate
- 45% Less Fat


< iframe src="http://www.facebook.com/plugins/like.php?href=http%3A%2F%2Fwww.oursite.com%2Findex.php%3Fmain_page%3Dproduct_info%26cPath%3D33_36%26products_id%3D106&layout=standard&show_faces=true&width=450&action=like&colorscheme=light&height=80" scrolling="no" frameborder="0" style="border:none; overflow:hidden; width:450px; height:80px;" allowTransparency="true">< /iframe>

I want to remove the <iframe and everything past it. I have been looking around for examples using REPLACE and/or LEFT, but can't find what I need, as I am not very well versed in SQL language.

It will always be at the end of the products_description field, so I don't need to worry about saving anything past it, only before it.

The best I can come up with is 

SELECT LEFT(REPLACE('<iframe%','<iframe%',' '),0)

but that doesn't seem to work.

Thank you for any help you can offer on this, as it will take me much less time than editing each description.

UPDATE: Still haven't found an answer, after trying many variations of what has been suggested. It runs, but makes no edit to the column.

I also tried this, after finding it on another site. The same, but thought it might give someone an idea of how to proceed:

select left(`products_description`,instr('<iframe',`products_description`)-1) FROM products_description
user1780464
  • 43
  • 6

3 Answers3

2
SELECT 
trim( -- trim removes spaces before and after given string
    left(
        'some text < iframe', 
        locate('< iframe', 'some text < iframe') - 1
    )
);

For better understanding i did not removed space between < and iframe.

You may also take a look at this thread:

Remove HTML tags from record

but this is discussion about removing tags only, and keeping <tag>text</tag> between tags. Anyway it will for for you, because you have nothing between <iframe> and </iframe>.

Community
  • 1
  • 1
Kamil
  • 13,363
  • 24
  • 88
  • 183
  • Thanks, had to put the space there or it wouldn't let me display the code. AFA the 'some text', am I able to use a wildcard (%, right?) there? all the descriptions are different, so I can't specify what will be there. Or is the 'some text' part of the code? – user1780464 Oct 30 '12 at 05:28
  • This: 'some text < iframe' is input string. Replace it with your database field name in both places of my example code. And you cant use wildcards in LOCATE. – Kamil Oct 30 '12 at 05:34
  • `SELECT trim( left( 'products_description', locate(' – user1780464 Oct 30 '12 at 05:44
  • 1. Have you added "FROM table" in your code? 2. Maybe put `< iframe` instead of ` – Kamil Oct 30 '12 at 05:55
  • 1. I am in phpMyAdmin, and am navigating to the table in question, then running the code from there, is it still necessary? – user1780464 Oct 30 '12 at 06:21
  • 2. the actual string I am wanting to begin with, and remove everything after, is – user1780464 Oct 30 '12 at 06:21
1
  1. REPLACE('<iframe%','<iframe%',' ') will give you just ' ' - it searches first argument for second argument and replaces it with third.
  2. LEFT(somestring, 0) will give you 0 characters string
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
0

Try this:

select substr(your_raw_string, 0, instr(your_raw_string, '<iframe') -1)
from your_table
bcmacariola
  • 106
  • 2
  • 15
  • Thank you. I tried this, and something similar to this I found on another site, posted in my edit above, using left. It runs fine, but shows no changes. I am not understanding what I am doing incorrect. – user1780464 Oct 30 '12 at 06:58
  • did you try substr()? This would only return the substring – bcmacariola Oct 30 '12 at 07:09
  • Yeah, this is my code I used: select substr(`products_description`, 0, instr(`products_description`, ' – user1780464 Oct 30 '12 at 18:02
  • column and table names are both products_description. When I run it, I don't receive an error message, but no changes are made. Using phpMyAdmin – user1780464 Oct 30 '12 at 18:05
  • Try: `select substr(PD.products_description, 0, instr(PD.products_description, ' – bcmacariola Oct 30 '12 at 22:57
  • Just tried that. I don't receive an error message, but performs the same way as your previous code. It gives me the results shown in the image below: [http://i.imgur.com/pfYjx.jpg] Is it supposed to show the changed lines? No lines are being edited. Thanks – user1780464 Oct 31 '12 at 23:43
  • Try placing a space in between < and frame `select substr(PD.products_description, 0, instr(PD.products_description, '< iframe') -1) from products_description PD` – bcmacariola Oct 31 '12 at 23:59
  • OK, tried that, didn't work. [Here](http://i.imgur.com/XRMDA.jpg) is something I noticed, when I clicked Inline Edit, it doesn't let me edit it. However, when I go to [Edit](http://i.imgur.com/EGvdj.png) it will allow me. Not sure why this is doing it, but thought it might shed some light on the trouble we are having. – user1780464 Nov 01 '12 at 03:18