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
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
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.
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.
corporate_names WHERE id='1s'; to accomplish ths
SELECT * FROM corporate_names WHERE id LIKE '1s';
SELECT * FROM corporate_names where CAST(id as char(255)) = '1s'. thank you.