0

I have an excel sheet for insert statement, in excel sheet there is no white spaces, but when inserted in mysql database. the insertion is successful but the data have white spaces in it.

how to excluded?

Regards:

Muhammad
  • 267
  • 4
  • 9
  • 24

4 Answers4

2

Either http://php.net/trim or http://php.net/str_replace? As in: str_replace ( ' ' , '' , $str );

Diederik
  • 602
  • 9
  • 24
2

For a different answer I.E. SQL based.

SELECT TRIM(' leading trailing ')

To use in an INSERT you could 

INSERT INTO Users (forename, surname) VALUES (TRIM(' ASHLEY '), TRIM(' MEDWAY '))
Ashley Medway
  • 7,151
  • 7
  • 49
  • 71
  • trim didn't work: INSERT INTO tbl_business ( name, description, address_1, address_2, location, region, country_iso, contact_person, postal_code, ph_office, ph_cell, fax, email, website, category_id, user_id, is_approved) VALUES (TRIM(' this is name '),TRIM(' this is desc '), ' this is address 1 ',' thisis address 2 ', 69 , 1 ,' PK ',' thi sis contact person ',' 44000 ',' 3434343 ',' this is cell ',' this is fax ',' admin@email.com ',' www.google.com ', 1 ,1,1); – Muhammad Feb 02 '14 at 11:37
  • there is no error, the white spaces also inserted. I want to not insert these. – Muhammad Feb 02 '14 at 12:01
1

Use PHP trim() function

http://in1.php.net/trim

<?php
$str="   This is a test";
echo trim($str);
?>
Jenson M John
  • 5,499
  • 5
  • 30
  • 46
-1

copy queries from excel to notepad/textpad and remove white/tab spaces by CTRL+H. and run the the statements. basically excel adds tab white spaces between columns. in case of strings, tab spaces are added in between double quotes("").

dvrnaidu
  • 151
  • 1
  • 1
  • 9
  • but I need to collect the data in excel sheet and then copy and paste to mysql console. is there any way to trim these spaces. – Muhammad Feb 02 '14 at 11:39