Mysql Select data with spaces

Question

I have a problem in selecting a data

Sample data

" test1   "
"test2    "
"test3"

How do i get the data using this query sample?

SELECT * FROM `data` Where data_name = "test2"

is there a way that it would work something like this. I know the code is wrong but is there a way it would work like this one?

SELECT * FROM `data` Where trim_spaces(data_name) = "test2"

i don't wan't to use this one because if there is a data_name = "test2 test" it will get it also.

SELECT * FROM `data` Where data_name like "%test2%"

Just adding, unless you have a very good reason to store the strings with spaces, you should trim them before inserting. — augustomen
– augustomen, Commented Nov 4, 2013 at 10:56
just wanna make sure the data has no space.i tried to get the data using this query SELECT * FROM data` Where data_name like "%test2%"` and it returns a data but when i use the SELECT * FROM data` Where data_name = "test2"` or SELECT * FROM data` Where TRIM(data_name) = "test2"` i didn't get anything — Cindy93
– Cindy93, Commented Nov 4, 2013 at 11:12

Guillaume Poussel · Accepted Answer · 2013-11-04 10:55:13Z

2

You may have a look at TRIM() function.

Sample usage:

SELECT *
FROM `data`
WHERE TRIM(data_name) = "test2"

If you also want to select trimmed values, you can use it in SELECT clause as well.

answered Nov 4, 2013 at 10:55

Guillaume Poussel

9,8522 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Cindy93 Over a year ago

thank.. i got a problem..i still don't get a value even the value exist in the database

Strawberry Over a year ago

In that case, we'll really need to see actual DDLs. Consider posting up an sqlfiddle.

Cindy93 Over a year ago

hello..just need a little help..i found the problem..i edit the data on the database. i found out that there was a space on it. so i remove all the space and save it and the = is now working. how come does the trim didn't remove it?

Guillaume Poussel Over a year ago

Because you have a new-line (\n), not only spaces. Use this instead: TRIM(REPLACE(city_name, "\n", "")) = 'Abanda'.

Cindy93 Over a year ago

just wanna ask :) what if the word is abanda city with \n between abanda and city how can i compare it with abanda city using = then? i tried the replace but not working REPLACE(city_name, "\n", " ")

|

Collectives™ on Stack Overflow

Mysql Select data with spaces

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related