Replace with Wildcard

#11

On 23-11-10 16:34, pod wrote:

Quote:

Luuk schrieb:
On 23-11-10 15:31, pod wrote:
Thorsten schrieb:
On 11/23/2010 11:15 AM, Thorsten wrote:
On 11/23/2010 10:36 AM, pod wrote:
Luuk schrieb:
On 23-11-10 10:08, pod wrote:
I want to delete the strings that have strings
could you be more precise on what you want to delete?
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?
I would use the statement like
... where column like 'image%jpg' ...
you can do a select before actually replacing them, just to see if you
got all rows.
Cheers
Thorsten
sry my bad. I didn't get it right.
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

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=%}'

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

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

My expample was just a SELECT, i hope you can rewirte that to an UPDATE
yourself....

But now i see i forgot to delete the numbers......,
So this solution wont work for you

Better do it in PERL, PHP, SED, AWK or some other tool....

--
Luuk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Replace with Wildcard

comp.databases.mysql comp.databases.mysql

Re: Replace with Wildcard - 11-23-2010 , 09:54 AM