Needs your help, both of following sql syntax produce same result in MySql

slamet_mjk · #1 (**permalink**) 12-22-09, 03:12

Hi All,

Needs your help, both of following sql syntax produce same result in MySql:

(1)FilterValue without space

Select * from SomeTable
where Column1 = 'FilterValue';

(2)FilterValue with space

Select * from SomeTable
where Column1 = 'FilterValue ';

How to configure, so both of sql syntax produce different result ?

Thanks.

healdem · #2 (**permalink**) 12-22-09, 03:39

Quote:

Originally Posted by slamet_mjk

...How to configure, so both of sql syntax produce different result ?.

they give different results because you are asking a different question and the two statements are mutually exclusive.

Code:

where Column1 = 'FilterValue';

is not the same as

Code:

where Column1 = 'FilterValue ';

the options are

Code:

where Column1 = 'FilterValue' or Column1 = 'FilterValue ';

or use a wildcard match

Code:

where Column1 like 'FilterValue%';

or just search column1 that starts with FilterValue

Code:

where Column1 like 'FilterValue';

or you may be able to use the in predicate

Code:

where Column1 in (blah, blahdi, blahdiblah);

so I'd suggest you look at the MySQL manual for wildcard searches or matches

shammat · #3 (**permalink**) 12-22-09, 03:56

Quote:

Originally Posted by healdem

they give different results because you are asking a different question and the two statements are mutually exclusive.

The question was how to get different results. The OP is getting the same result for both queries.

Quote:

Originally Posted by slamet_mjk

How to configure, so both of sql syntax produce different result ?

You neither stated the data type you are using nor the MySQL version you are using.

But I assume you are affected by the following situation:

Quote:

Originally Posted by MySQL Manual

As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values

Taken from http://dev.mysql.com/doc/refman/5.0/en/char.html

Internet Services

Web Development

Digital Marketing

Consumer Tech