We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

mysql search characters and update

Victor Kimura
on
Medium Priority
468 Views
Last Modified: 2012-06-21
Hi,

I'm trying to update some fields where 'product_descr' contains the characters:
[space]&[space]

and change it to:
[space]&[space]

As well, I'm trying to find this character:

(this is supposed to find the special MS Word single quote which usually messes things up when rendered in a browser)

and change it to:
' or to '

I have other special French characters to like:
è

that need to be changed to:
è




Can someone help create this update sql statement?

Thank you,
Victor
CREATE TABLE skinca_dermapproved.products(
  product_id INT(11) NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  product_type TINYINT(2) NOT NULL,
  product_skin_type TINYTEXT NOT NULL,
  product_brand VARCHAR(40) NOT NULL,
  product_sub_brand VARCHAR(40) NOT NULL,
  product_size VARCHAR(50) NOT NULL,
  product_price FLOAT NOT NULL,
  product_intnum VARCHAR(20) NOT NULL,
  product_actives TEXT NOT NULL,
  product_active_ingr TEXT NOT NULL,
  product_descr TEXT NOT NULL,
  product_image INT(11) NOT NULL DEFAULT 1,
  product_url TINYTEXT NOT NULL,
  product_notes TEXT NOT NULL,
  product_score TINYINT(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1002
AVG_ROW_LENGTH = 899
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Open in new window

Comment
Watch Question

Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
try:

update yourtable set nama = REPLACE(nama, ' & ', ' & ');

for other string replacement, use same method as above.

Commented:
correction:

update yourtable set nama = REPLACE(yourfield, ' & ', ' & ');
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.