Member Avatar for millsy007

I am using the syntax: REPLACE(str,from_str,to_str)

to replace data in my table. However I have a slight issue I would like to address, when I have the following records

jon | jonathon | jonas | jonny

and I do the replace with

REPLACE(names, jon, '')

I am left with

| athon | as | ny

Is there a way that it can only replaces 'whole words' and not replace the string where it is part of a larger string?

  • 3 Contributors
  • 4 Replies
  • 87 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by manojjena1982
Member Avatar for dickersonka

add a where clause for
where str = 'jon' or str like ('jon %')

notice the space after jon

Member Avatar for millsy007

By a similar logic could I do something like:
REPLACE(str, from_str %, to_str)
As I am updating multiple columns adding where causes would make my code very verbose.
Thanks

Member Avatar for dickersonka

no, i don't believe the replace has that exact logic

show us your complete update statement or select whatever that may be, and lets go from there

Member Avatar for manojjena1982

Hi!
Try this

select if(name='Jon',replace(name,'Jon',''),name) as name from data

Regards,
Manoj

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.