how to remove spaces from a field in mySQL db?

Question

I have a database where i store my photos title, tags, and their path on sever some of the records have names like:
photo 1234.png. I need to make them like photo1234.png

Why can't I use a query like

UPDATE tblPhoto a
  set a.photoLink = replace(a.photoLink , ' ', '')
  where a.photoLink like '% %';

And which is the best way to rename them in Linux Server, can I use php ?

What do you mean by "can't I use a query like"? Did you try it? What happened? — Paul Spiegel, Dec 27 '19 at 14:36
I have safe mode turned ON and that throws this error " You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column." — 丹尺爪工れ, Dec 27 '19 at 14:46
Please edit your question and add that info. Also post how you execute that query (eg. workbench, CLI or some application language). — Paul Spiegel, Dec 27 '19 at 14:53

score 0 · Accepted Answer · answered Dec 27 '19 at 14:41

0

You don't need where clause

UPDATE tblPhoto SET photoLink = REPLACE(photoLink , ' ', '');

For replacing the file name on your Linux Server you can try to look in this answer. https://stackoverflow.com/a/2709619/7921383

answered Dec 27 '19 at 14:41

112Legion

1,129
12
25

score 0 · Answer 2 · answered Dec 27 '19 at 15:03

0

Use php method for example:

$old_name="Hello World";
echo str_replace(" ","",$old_name);

//output Helloworld

answered Dec 27 '19 at 15:03

Parth prajapati

62
2

how to remove spaces from a field in mySQL db?

2 Answers2