Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Changing just the first mention of a word in any given table?

11:37 am on Jul 28, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member sgt_kickaxe is a WebmasterWorld Top Contributor of All Time 5+ Year Member

joined:Apr 14, 2010
votes: 0

Changing EVERY iteration of a word in a table is simple with the REPLACE function. example

UPDATE table1
SET col1 = (REPLACE (col1, 'widget', 'bigwidget'))

But how would I write that to replace only the FIRST iteration of the word in the table? Or how would I make sure the word is not inside markup such as a link?

edit: in case it helps I want to replace keywords with links to their respective pages but only the first instance on any given page/table. I don't want to link the same keyword 20 times on a page.
11:58 am on July 28, 2011 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Aug 13, 2003
votes: 1

Instead of replace, you should find that INSTR should give you the position of the first word. Then you can use something along the lines of:

LEFT(col1,INSTR(col1)-1) + 'bigwidget' + MID(col1,INSTR(col1)+LENGTH(col1))

Probably not the most efficient way but it should work. It is psuedocode so you might need to lookup what should be used for LEFT, MID and LENGTH in the language you are using.