mysql remove white space

Question

I read on the forum about php function LTRIM, I used it in my code but still getting white space on the left side of the text, here is an example of my code, anyone could tell me what's wrong with the LTRIM function below?

$sql = "UPDATE content SET text = LTRIM('$content21') WHERE element_id = '21' ";

why dont you clean/trim the value before entering it in the sql? — Jonathan Römer, Jul 24 '13 at 12:17
@Jonathan, I have white space on the left side of the text when the text is saved to DB, I read that LTRIM would fix the issue... — Michel, Jul 24 '13 at 12:22
@Mr. Alien, please could you explain? this is php, I just did not include the in the sample code above — Michel, Jul 24 '13 at 12:23
$content21 = trim($content21); OR $content21 = ltrim($content21); — Bhavin Rana, Jul 24 '13 at 12:27
@Michel http://stackoverflow.com/questions/1703320/remove-whitespace-from-within-a-string — Mr. Alien, Jul 24 '13 at 12:28
Yes this is PHP but I think @Mr.Alien think its an SQL function. because its in your query. — Sebastien, Jul 24 '13 at 12:28
@Michel, check the answers below and see what i ment, with trimming the value before entering it in the sql query. — Jonathan Römer, Jul 24 '13 at 12:29
@Mr.Alien thanks for the pointers, I would not know how to make it work in: $foo = preg_replace( '/\s+/', ' ', $foo ); — Michel, Jul 24 '13 at 12:33

score 0 · Answer 1 · answered Jul 24 '13 at 12:17

0

Try to trim before the query like

$content21 = ltrim($content21);
$sql = "UPDATE content SET text = '$content21' WHERE element_id = '21' ";

answered Jul 24 '13 at 12:17

GautamD31

28,552
10
64
85

That wont work either. You are not refering to `$content21`'s value but `$content21` as a string itself – Sebastien Jul 24 '13 at 12:26

score 0 · Answer 2 · edited Jul 24 '13 at 13:29

0

$sql = "UPDATE `content` SET `text` = LTRIM('".$content21."') WHERE `element_id` = 21 ";

extract the var from string

edit based on comment, as there are tons of solutions for that:

$sql = "UPDATE `content` SET `text` = LTRIM('".htmlspecialchars($content21, ENT_QUOTES)."') WHERE `element_id` = 21 ";

edited Jul 24 '13 at 13:29

glglgl

89,107
13
149
217

answered Jul 24 '13 at 12:19

de_nuit

650
7
13

This wont work LTRIM is a php function you need to take the function out too. See my answer – Sebastien Jul 24 '13 at 12:24
There is an LTRIM function in mysql. – Musa Jul 24 '13 at 12:26
@Michel also pay attention to the quotation. – de_nuit Jul 24 '13 at 12:57
@de_nuit thanks I used copy/paste, yeah I noticed the quotation are different, there is still a white space in the DB before the text – Michel Jul 24 '13 at 13:03

score 0 · Answer 3 · answered Jul 24 '13 at 12:20

0

this should work:

$sql = "UPDATE content SET text = ".ltrim($content21)." WHERE element_id = '21'";

answered Jul 24 '13 at 12:20

Sebastien

1,308
2
15
39

It removes all the text after the ' so for instance I tried test's 123, in the DB, there is only: test – Michel Jul 24 '13 at 12:27
Also, it does remove the white space but it removes all the text after the apostrophe ' – Michel Jul 24 '13 at 12:28
Well this is exactly what `ltrim()` does it trim the left part of the string. – Sebastien Jul 24 '13 at 12:31
If you want to remove only the spaces you could use `$sql = "UPDATE content SET text = ".str_replace(" ", "", $content21)." WHERE element_id = 21";` – Sebastien Jul 24 '13 at 12:34
I thought the ltrim removes white space only? It removes any sentences after an apostrophe ' inside the DB so for instance "john's hat" is saved as "john" in the db – Michel Jul 24 '13 at 12:44
What I wanted to say is that `ltrim($str, $char)` only remove from the left of the file and it does'nt remove only whitespaces you can Specified characters to be removed. – Sebastien Jul 24 '13 at 15:21
And your `'` is marking the end of the sql string you'll have to use another function to take care of the `'` in `"John's"`. – Sebastien Jul 24 '13 at 15:24
or you could use `ltrim($content21, "'");` – Sebastien Jul 24 '13 at 15:25

mysql remove white space

3 Answers3