CODEWITHSUNDEEP
mysql
Vishal B
Vishal B

Reputation: 653

mysql query compare string with int value

I am trying to make select query as follow

SELECT * FROM `corporate_names` WHERE id = '1s';

but it still gives me result as there is no column id with value equals to '1s'. it reslts me of SELECT * FROM corporate_names WHERE id = '1'; result.

I am not getting why isthis happening? how can i exactly match id column with string value as id is primary key with int column type.

i want it to exact match the column value

Upvotes: 1

Views: 3919

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa. example 

SELECT 1+'1'; -> 2

The following rules describe how conversion occurs for comparison operations:

For details you can read documentation

But you can stop it following way

SELECT case when  CONVERT('1s' USING utf8) = cast( 1 as char) then 2 else 0 end; this will return 0 .

so explicit conversion is the way of stopping this or solving your problem

Upvotes: 4

Vishal B
Vishal B

Reputation: 653

I solve this as below

SELECT * FROM `corporate_names` where CAST(id as char(255)) = '1s'.

now it is exactly matching value with column value, i just cast id column to char.

Upvotes: 3

Usman Ali
Usman Ali

Reputation: 58

if you would like to get all the corporate names where the id starts with 1 then use LIKE as shown below:

SELECT * FROM `corporate_names` WHERE id LIKE '1%';

Else if you would like to retrieve the results for just a specific id use the following:

SELECT * FROM `corporate_names` WHERE id='1';

I hope that helps.

Upvotes: 0

Related Questions