Tricky Query Help....is this even possible? (I love laying down a challenge)

[bassmo]

Hi all,

 

I have a rather annoying problem.  I need to be able to run a replace on a string.  In itself obviously not a problem.  However I only need to replace the occurrence of the string when it is at the start of the string.  Example:

 

Need to replace Apple with Orange:

 

AppleBananaPeach      to become  OrangeBananaPeach

ApplePearPlumApple    to become  OrangePearPlumApple

BananaApple                to remain  BananaApple

 

All strings are a single "word", no spaces.

 

The thought occurs to me to place a marker at the start.  Something along the lines of:

 

|ApplePearPlumApple      ->  Replace      |Apple with Orange    ->    OrangePearPlumApple

 

Any idea if I can do this?

 

Many thanks in advance.

 

Bassmo / Jon

[bassmo]

Actually not tricky - just done it myself....sorry

 

update testtable set myfield = replace(concat('|',myfield,'|apple','orange');

 

[Muddy_Funster]

Or if you only want to show a different word without changing the data in the table you could use this, handy for masking the start of sensetive information, such as card no's and the like

SELECT REPLACE((SELECT `text` FROM table where left(`text`, Length('WordToReplace')) = 'WordToReplace'), 'WordToReplace', 'NewWord')