This is SALESMAN table in MySQL:
Scode | Sname | Area | Qtysold | Dateofjoin |
---|---|---|---|---|
S001 | Ravi | North | 120 | 2015-10-01 |
S002 | Sandeep | South | 105 | 2012-08-01 |
S003 | Sunil | NULL | 68 | 2018-02-01 |
S004 | Subh | West | 280 | 2010-04-01 |
S005 | Ankit | East | 90 | 2018-10-01 |
S006 | Raman | North | NULL | 2019-12-01 |
You can check it here in dbfiddle.uk.
The output of SELECT Sname FROM SALESMAN WHERE RIGHT(Scode,1)=5;
is:
+-------+
| Sname |
+-------+
| Ankit |
+-------+
1 row in set (0.00 sec)
The output of SELECT Sname FROM SALESMAN WHERE RIGHT(Scode,1)='5';
is:
+-------+
| Sname |
+-------+
| Ankit |
+-------+
1 row in set (0.00 sec)
Scode column has CHAR datatype. So, output of SELECT RIGHT(Scode,1) FROM SALESMAN
should be a text right? As far as I know, 5!='5'. Then how did the output of both the queries be same? Shouldn't output of SELECT Sname FROM SALESMAN WHERE RIGHT(Scode,1)=5;
be an empty set as Scode has CHAR as datatype?
I used the logic from this answer and ran SELECT RIGHT(Scode,1) FROM SALESMAN;
. This is the output:
Field 1: `RIGHT(Scode,1)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: cp850_general_ci (4)
Length: 1
Max_length: 1
Decimals: 31
Flags:
+----------------+
| RIGHT(Scode,1) |
+----------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----------------+
6 rows in set (0.00 sec)
So, the data type of output of SELECT RIGHT(Scode,1) FROM SALESMAN;
is VAR_STRING. I guess VAR_STRING refers to VARCHAR.