如何在 SQL 中将 Varchar 转换为 Double?
mysqlmysqli database
您可以使用 CAST() 函数将 varchar 转换为 double。语法如下:
SELECT yourColumnName1,yourColumnName2,......N, CAST(yourColumnName AS DECIMAL(TotalDigit,DigitAfterDecimalPoint)) anyVariableName FROM yourtableName ORDER BY anyVariableName DESC;
为了理解上述语法，让我们创建一个表。创建表的查询如下:
mysql> create table VarcharToDouble -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> Amount varchar(10) , -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.67 sec)
使用 insert 命令在表中插入一些记录。 查询语句如下:
mysql> insert into VarcharToDouble(Name,Amount) values('John','456.78');
Query OK, 1 row affected (0.14 sec)
mysql> insert into VarcharToDouble(Name,Amount) values('Larry','8465.98');
Query OK, 1 row affected (0.18 sec)
mysql> insert into VarcharToDouble(Name,Amount) values('Mike','9083.45');
Query OK, 1 row affected (0.12 sec)
mysql> insert into VarcharToDouble(Name,Amount) values('Sam','46475.50');
Query OK, 1 row affected (0.19 sec)
mysql> insert into VarcharToDouble(Name,Amount) values('Carol','10003.35');
Query OK, 1 row affected (0.17 sec)
使用 select 语句显示表中的所有记录。查询如下:
mysql> select *from VarcharToDouble;
输出结果如下:
+----+-------+----------+ | Id | Name | Amount | +----+-------+----------+ | 1 | John | 456.78 | | 2 | Larry | 8465.98 | | 3 | Mike | 9083.45 | | 4 | Sam | 46475.50 | | 5 | Carol | 10003.35 | +----+-------+----------+ 5 rows in set (0.00 sec)
以下是使用 CAST() 将 varchar 转换为 double 的查询:
mysql> select Id,Name, CAST(Amount AS DECIMAL(10,2)) TotalAmount from VarcharToDouble -> order by TotalAmount desc;
输出结果如下:
mysql> select Id,Name, CAST(Amount AS DECIMAL(10,2)) TotalAmount from VarcharToDouble -> order by TotalAmount desc;
