SQL订单字符串作为数字

Question

我将数字保存VARCHAR到MySQL数据库。INT由于某些其他情况，我无法制作它们。排序时将其作为字符而不是数字。在数据库中我有1 2 3 4 5 6 7 8 9 10...在我的页面上，它显示如下排序列表：1 10 2 3 4 5 6 7 8 9如何使它按数字升序显示？

Answer 1

如果可能，则无论如何仅存储数字，应将列的数据类型更改为数字。

如果您无法执行此操作，则将列值integer 强制转换为

select col from yourtable

order by cast(col as unsigned)

或隐式地使用例如数学运算来强制转换为数字

select col from yourtable

order by col + 0

BTW MySQL将字符串从左到右转换。例子：

string value  |  integer value after conversion

--------------+--------------------------------

'1'           |  1

'ABC'         |  0   /* the string does not contain a number, so the result is 0 */

'123miles'    |  123 

'$123'        |  0   /* the left side of the string does not start with a number */

Answer 2

我要排序的列具有字母和数字的任意组合，因此我以本文中的建议为起点，并提出了建议。

DECLARE @tmp TABLE (ID VARCHAR(50));

INSERT INTO @tmp VALUES ('XYZ300');

INSERT INTO @tmp VALUES ('XYZ1002');

INSERT INTO @tmp VALUES ('106');

INSERT INTO @tmp VALUES ('206');

INSERT INTO @tmp VALUES ('1002');

INSERT INTO @tmp VALUES ('J206');

INSERT INTO @tmp VALUES ('J1002');

SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum

FROM @tmp

ORDER BY IsNum, LEN(ID), ID;

结果

ID

------------------------

106

206

1002

J206

J1002

XYZ300

XYZ1002

希望这可以帮助

Answer 3

另一种转换方式。

如果您有字符串字段，则可以按以下方式对其进行转换或将其数字部分转换：添加前导零以使所有整数字符串具有相同的长度。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( stringfield ) ) , stringfield ) 

或按字段的一部分排序，例如“ tensymbols13”，“ tensymbols1222”等。

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) )