在 MySQL 查询中将文本转换为数字 [英] Convert text into number in MySQL query

问题描述

是否可以在 MySQL 查询中将文本转换为数字?我有一个带有标识符的列，该标识符包含一个名称和一个格式为名称-编号"的数字.该列具有 VARCHAR 类型.我想根据数字(具有相同名称的行)对行进行排序，但列是根据字符顺序排序的，即

Is it possible to convert text into a number within MySQL query? I have a column with an identifier that consists a name and a number in the format of "name-number". The column has VARCHAR type. I want to sort the rows according to the number (rows with the same name) but the column is sorted according to do character order, i.e.

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

如果我截断数字，是否可以将varchar"数字转换为真实"数字并使用它对行进行排序?我想获得以下订单.

If I cut off the number, can I convert the 'varchar' number into the 'real' number and use it to sort the rows? I would like to obtain the following order.

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

我无法将数字表示为单独的列.

I cannot represent the number as a separate column.

编辑于 2011-05-11 9:32

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

I have found the following solution ... ORDER BY column * 1. If the name will not contain any numbers is it safe to use that solution?

推荐答案

这应该有效:

SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;

这篇关于在 MySQL 查询中将文本转换为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！