Find and Replace text in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find and Replace text in table

Author

Topic

voxmaris
Starting Member

3 Posts

Posted - 2008-11-20 : 11:29:58

I have various images paths in my database all with unique "height" and "width" attributes. Here are some examples:

img height="25" alt="" width="116" src=
img height="18" alt="" width="115" src=
img height="27" alt="" width="113" src=
img height="39" alt="" width="154" src=
img height="42" alt="" width="114" src=

I am wondering what the easiest way to do a Search and Replace string to remove everyting between img and src and just make the code say "img src="

I have searched high and low for this and have tried all kinds of wildcards including underscores and % signs and nothing works.

Please if someone could help me. This was my latest attempt:

update tablename set fieldname = replace(fieldname, 'img% src=', 'img src=');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:34:12

[code]update tablename set fieldname = LEFT(fieldname,PATINDEX('%img%',fieldname)-1)+ SUBSTRING(fieldname,PATINDEX('%src%',fieldname),LEN(fieldname))
WHERE PATINDEX('%img%',fieldname)>0
AND PATINDEX('%src%',fieldname)>0[/code]

voxmaris
Starting Member

3 Posts

Posted - 2008-11-21 : 15:10:34

I tried that command and I received the following error:

#1305 - FUNCTION dbase_name.PATINDEX does not exist

Did I do something incorrect?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-11-21 : 15:45:37

Can you post the exact SQL you used? Are you using SQL Server?

voxmaris
Starting Member

3 Posts

Posted - 2008-11-21 : 15:54:23

I am using PHPmyAdmin. SQL command:

I input this:

update jos_content set introtext = LEFT(introtext,PATINDEX('%img%',introtext)-1)+ SUBSTRING(introtext,PATINDEX('%src%',introtext),LEN(introtext))
WHERE PATINDEX('%img%',introtext)>0
AND PATINDEX('%src%',introtext)>0

This is what I get:

SQL query:

UPDATE jos_content SET introtext = LEFT( introtext, PATINDEX(

'%img%',
introtext
) -1 ) + SUBSTRING( introtext, PATINDEX(
'%src%',
introtext
), LEN(
introtext
) ) WHERE PATINDEX(
'%img%',
introtext
) >0 AND PATINDEX(
'%src%',
introtext
) >0

MySQL said:

#1305 - FUNCTION root_jo151.PATINDEX does not exist

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-11-21 : 19:11:05

My guess is that MySQL does not have a PATINDEX function. SQL Team is a SQL Server site, we don't specialize in MySQL questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources