CODEWITHSUNDEEP
mysqlsqlwhitespace
Dave
Dave

Reputation: 19150

How do I make extra white space affect my MySQL query result?

I'm using MySQL 5.5.37. If I run the following query on my database

select count(*) FROM user where user_name = 'admin';

I get back the result "3". However, when I execute the query

select count(*) FROM user where user_name = 'admin     ';

, (notice the extra spaces after the word "admin", I also get back the result "3". In fact, when i look at teh records, they are the same records as the first query. However, there are no records in my databaes with the user_name field equal to "admin ". Given that I cannot upgrade my MySQL database at this time, what can I do to ensure that the second query (wiht the extra white space) returns the correct result (i.e. "0")?

Edit: The user_name field is of type varchar(50).

Upvotes: 0

Views: 677

Answers (3)

Zhengde Zhen
Zhengde Zhen

Reputation: 36

 select count(*) FROM [sb_user] where user_name = 'admin     ';

you may try this one

Upvotes: 0

mproffitt
mproffitt

Reputation: 2527

You could try using a regular expression as described in this post? Query for exact match of an string in SQL

Upvotes: 0

Marandil
Marandil

Reputation: 1052

I'm not 100% sure that it's the case, but it looks like your user_name field is a char(n) field and not varchar(n).

Such fields are filled with whitespaces to fully match the length of the field, while varchars are (I believe) null terminated strings.

Both queries return the same answer, as they also fill the missing characters with whitespaces.

If you want the query to work properly, consider changing the type of the column to varchar.

Upvotes: 1

Related Questions