在 MySQL 查询中将部分非数字文本转换为数字

是否可以在 MySQL 查询中将文本转换为数字？我有一个标识符列，其中包含一个名称和一个“ name-number”格式的数字。该列具有 VARCHAR 类型。我想根据数字(名称相同的行)对行进行排序，但是列是按照字符顺序排序的，即。

name-1
name-11
name-12
name-2

如果我截断了这个数字，我是否可以将“ varchar”数字转换成“实数”数字并使用它对行进行排序？我想获得下列订单。

name-1
name-2
name-11
name-12

我不能将这个数字表示为一个单独的列。

编辑: 2011-05-119:32

我已经找到了下面的解决方案 ... ORDER BY column * 1。如果名称不包含任何数字，使用该解决方案是否安全？

608843 次浏览
小开

可以使用 CAST ()将字符串转换为 int

小开

要获得数字尝试与 SUBSTRING_INDEX(field, '-', 1)然后转换。

小开

你可以使用 SUBSTRING和 CONVERT:

SELECT stuff
FROM table
WHERE conditions
ORDER BY CONVERT(SUBSTRING(name_column, 6), SIGNED INTEGER);

其中 name_column是具有“ name-”值的列。SUBSTRING删除第六个字符之前的所有字符(即“ name-”前缀) ，然后 CONVERT将剩下的字符转换为实数。

更新 : 考虑到注释中不断变化的情况(即前缀可以是任何内容) ，您必须在混合中添加一个 LOCATE:

ORDER BY CONVERT(SUBSTRING(name_column, LOCATE('-', name_column) + 1), SIGNED INTEGER);

当然，这里假设非数字前缀没有任何连字符，但是相关的注释说:

name可以是任意序列的字母

所以这应该是一个安全的假设。

小开
最佳答案

这应该会奏效:

SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;
小开 
SELECT *, CAST(SUBSTRING_INDEX(field, '-', -1) AS UNSIGNED) as num FROM tableName ORDER BY num;
小开

如果您的主键是格式为

ABC/EFG/EE/13/123(序列号)
这种类型的字符串可以很容易地使用分隔符(“/”)进行排序

我们可以使用以下查询对具有此类键的表进行排序

SELECT * FROM `TABLE_NAME` ORDER BY
CONVERT(REVERSE(SUBSTRING(REVERSE(`key_column_name`), 1, LOCATE('/', REVERSE(`key_column_name`)) - 1)) , UNSIGNED INTEGER) DESC
小开

一个简单的方法选择’123’+ 0

小开

简单地使用 CAST,

CAST(column_name AS UNSIGNED)

强制转换结果的类型可以是下列值之一:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
小开

一个通用的方法:

SELECT * FROM your_table ORDER BY LENTH(your_column) ASC, your_column ASC
小开 
cast(REGEXP_REPLACE(NameNumber, '[^0-9]', '') as UNSIGNED)
小开 
select
`a`.uuid,
concat('1',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`a`.uuid,'-',''),'b','11'),'c','12'),'d','13'),'e','14'),'f','15'),'a','10')),
小开

我发现使用 regex _ place 函数从字段中去掉所有非数值并进行排序更容易。

SELECT field , CONVERT(REGEXP_REPLACE(field,'[^0-9]',''),UNSIGNED) AS num FROM your_table ORDER BY num;

提交答案