Hello

I have a column varchar(255) and a few rows with text. In all rows I
want to delete the strings that have strings e.g. "{image=23.jpg}",
whereas the numbers can change. Therefore I need a wildcard for the
number. I have read that REPLACE doesnt support wildcards, so is there a
common way to delete that strings?

thx pod

On 23-11-10 10:08, pod wrote:
could you be more precise on what you want to delete?

--
Luuk

Luuk schrieb:
all these strings -> {image=23.jpg}
brackets included, that can be found in the text
the image is always different {image=50.jpg}, {image=100.jpg} etc. so
for the number I need a wildcard?

On 11/23/2010 10:36 AM, pod wrote:
.... where column like 'image%jpg' ...
you can do a select before actually replacing them, just to see if you
got all rows.
Cheers
Thorsten

On 23-11-10 11:15, Thorsten wrote:
the select could look like:
SELECT column, REPLACE(REPLACE(column,'{image=',''),'.jpg}','')
FROM table
WHERE column LIKE '{image=%.jpg}'

--
Luuk

On 11/23/2010 11:15 AM, Thorsten wrote:
You can't do that with replace but you can still do it in mysql. The
complete statement would look like this
update TABLE set COLUMN = concat(substring( COLUMN, 1, locate(
'{image=', COLUMN ) -1 ) , substring( COLUMN, locate( 'jpg}', COLUMN ) +4 ))

As you can see this isn't a really good way. Use a script to do the
update. In perl you can use preg_replace for that.
Cheers
Thorsten

Thorsten schrieb:

hello thorsten hello luuk

As I see it correctly you thorsten try to create a new string with the
text before and after the brackets and set that new string but the row
is a textfield and sometimes it can be that a few images appear in the
text something like this:

bla bla {image cw=12} bla foo foo {image cw=23} lba bla bla

and i want as a result bla bla bla foo foo lba bla bla

and this statement

SELECT column, REPLACE(REPLACE(column,'{image cw=',''),'}','')
FROM table
WHERE column LIKE '{image cw=%}'

doesnt work I guess because the image is not the only content and
therefore it doesnt get found LIKE cannot find it? there is some text
around as i mentionend before.

e.g. I get no rows back SELECT * from content WHERE text LIKE '{image cw=%}'

On 11/23/2010 03:31 PM, pod wrote:
My statement will work for the phrase '{image=23.jpg}' but not for the
phrase '{image cw=23}'. But as I already mentioned it is much easier
with perl or php.
Something like this

$text = preg_replace('/{image(.*)[0-9]*(\.jpg)?}/', '', $text);

This would match '{image=23.jpg}' and also '{image cw=23}'
Cheers
Thorsten

On 23-11-10 15:31, pod wrote:
the '%' is the wilcard char, so try:

SELECT column, REPLACE(REPLACE(column,'{image cw=',''),'}','')
FROM table
WHERE column LIKE '%{image cw=%}%'

for a complete explanation of the LIKE:
http://dev.mysql.com/doc/refman/5.1/...functions.html

--
Luuk

Luuk schrieb:
Hello

the statement

SELECT mosimage, REPLACE(REPLACE(mosimage,'{image cw=',''),'}','')
FROM mosimage
WHERE mosimage LIKE '%{image cw=%}%'

on the row with content: foo foo {image cw=23} sdf sdf {image cw=455}

gives: foo foo 23 sdf sdf 455

but the row itself is still not updated so i guess an update command is
missing somewhere and the row should then be filled with: foo foo sdf sdf

thx pod