Search code examples
sqlmariadbvarchar

multiply two varchars in sql


This what I have tried but it is giving errors:

MariaDB [test]> create table table1 (length varchar, breadth varchar);                                                             
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' breadth varchar)' at line 1
MariaDB [test]> create table table1 (length varchar(20), breadth varchar(20));                                                         
Query OK, 0 rows affected (0.06 sec)
MariaDB [test]> insert into table1 (length, breadth) values ('12','11');                                                                                                                               
Query OK, 1 row affected (0.05 sec)                                                                                                                                                                    

MariaDB [test]> select * from table1;                                                                                                                                                                  
+--------+---------+                                                                                                                                                                                   
| length | breadth |                                                                                                                                                                                   
+--------+---------+                                                                                                                                                                                   
| 12     | 11      |                                                                                                                                                                                   
+--------+---------+                                                                                                                                                                                   
1 row in set (0.02 sec)                                                                                                                                                                                                                                                                                                                                                                                   
MariaDB [test]> select convert (int, length)*convert(int, breadth) as T from table1;                                                                                                                   
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'int, length)*convert(int, breadth) as T fr
om table1' at line 1                                                                                                                                                                                   
MariaDB [test]> select convert(int, length)*convert(int, breadth) as T from table1;                                                                                                                    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'int, length)*convert(int, breadth) as T fr
om table1' at line 1                                                                                                                                                                                   

Solution

  • Use cast():

    select cast(length as int) * cast(breadth as int) as T
    from table1;  
    

    As explained in the documentation, convert() is for converting between different character sets. You are confusing MySQL's (and MariaDB's) use of convert() with SQL Server's.

    By the way, you do not even need the explicit cast() (at least in MySQL). The engine will do an implicit cast for you:

    select length * breadth as T from table1;  
    

    Although this is supported, I don't really advocate depending on implicit casts.

    Here is a SQL Fiddle.