在 MySQL 查询中将文本转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5960620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert text into number in MySQL query
提问by czuk
Is it possible to convert text into 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 the number (rows with the same name) but the column is sorted according do character order, i.e.
是否可以在 MySQL 查询中将文本转换为数字？我有一个带有标识符的列，该标识符包含一个名称和一个格式为“名称-编号”的数字。该列具有 VARCHAR 类型。我想根据数字（具有相同名称的行）对行进行排序，但列是根据字符顺序排序的，即
name-1
name-11
name-12
name-2
If I cut of the number, can I convert the 'varchar' number into the 'real' number and use it to sort the rows? I would like to obtained following order.
如果我削减了数字，我可以将“varchar”数字转换为“真实”数字并使用它对行进行排序吗？我想获得以下订单。
name-1
name-2
name-11
name-12
I cannot represent the number as a separate column.
我不能将数字表示为单独的列。
edited 2011-05-11 9:32
编辑 2011-05-11 9:32
I have found following solution ... ORDER BY column * 1. If the name will not contain any numbers is it save to use that solution?
我找到了以下解决方案... ORDER BY column * 1。如果名称不包含任何数字，是否可以保存使用该解决方案？
回答by Marco
This should work:
这应该有效：
SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;
回答by mu is too short
You can use SUBSTRINGand CONVERT:
SELECT stuff
FROM table
WHERE conditions
ORDER BY CONVERT(SUBSTRING(name_column, 6), SIGNED INTEGER);
Where name_columnis the column with the "name-" values. The SUBSTRINGremoves everything up before the sixth character (i.e. the "name-" prefix) and then the CONVERTconverts the left over to a real integer.
name_column具有“名称-”值的列在哪里。在SUBSTRING移除了一切了第六个字符之前（即“名称- ”前缀），然后CONVERT转换遗留到一个真正的整数。
UPDATE: Given the changing circumstances in the comments (i.e. the prefix can be anything), you'll have to throw a LOCATEin the mix:
更新：鉴于评论中不断变化的情况（即前缀可以是任何内容），您必须LOCATE在混合中加入 a ：
ORDER BY CONVERT(SUBSTRING(name_column, LOCATE('-', name_column) + 1), SIGNED INTEGER);
This of course assumes that the non-numeric prefix doesn't have any hyphens in it but the relevant comment says that:
这当然假设非数字前缀中没有任何连字符，但相关评论说：
namecan be any sequence of letters
name可以是任何字母序列
so that should be a safe assumption.
所以这应该是一个安全的假设。
回答by Sibin John Mattappallil
Simply use CAST,
只需使用 CAST，
CAST(column_name AS UNSIGNED)
The type for the cast result can be one of the following values:
转换结果的类型可以是以下值之一：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
回答by verdesmarald
回答by Gaurav
SELECT *, CAST(SUBSTRING_INDEX(field, '-', -1) AS UNSIGNED) as num FROM tableName ORDER BY num;
回答by V R K RAO
one simple way SELECT '123'+ 0
一种简单的方法 SELECT '123'+ 0
回答by Harsha
if your primary key is a string in a format like
ABC/EFG/EE/13/123(sequence number)
this sort of string can be easily used for sorting with the delimiter("/")
如果您的主键是格式如
ABC/EFG/EE/13/123(sequence number)
的字符串，那么这种字符串可以很容易地用于使用分隔符（“/”）进行排序
we can use the following query to order a table with this type of key
我们可以使用以下查询来订购具有此类键的表
SELECT * FROM `TABLE_NAME` ORDER BY
CONVERT(REVERSE(SUBSTRING(REVERSE(`key_column_name`), 1, LOCATE('/', REVERSE(`key_column_name`)) - 1)) , UNSIGNED INTEGER) DESC
回答by Harsha
To get number try with SUBSTRING_INDEX(field, '-', 1)then convert.
要获得号码尝试SUBSTRING_INDEX(field, '-', 1)然后转换。
回答by Jayram Kumar
cast(REGEXP_REPLACE(NameNumber, '[^0-9]', '') as UNSIGNED)
回答by Azzu
A generic way to do :
一种通用的方法：
SELECT * FROM your_table ORDER BY LENTH(your_column) ASC, your_column ASC
