Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

mysql search characters and update

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

0
Victor Kimura
Asked:
Victor Kimura
  • 3
2 Solutions
 
Om PrakashCommented:
it will be something like:

Update skinca_dermapproved.products set product_descr = REPLACE(product_descr, 'w', 'Ww');
0
 
wuyinzhiCommented:
try:

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

for other string replacement, use same method as above.
0
 
wuyinzhiCommented:
correction:

update yourtable set nama = REPLACE(yourfield, ' & ', ' & ');
0
 
wuyinzhiCommented:
correction again :)) (wonder why there's no edit button here)

update yourtable set yourfield = REPLACE(yourfield, ' & ', ' & ');
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now