Convert text into number in MySQL query

Question

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  quot name-number quot   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  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  edited 2011-05-11 9 32 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

User · Answer

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("/")

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

User · Answer

one simple way SELECT  123   0

User · Answer

Simply use 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

User · Answer

You can use CAST   to convert from string to int  e g  SELECT CAST  123  AS INTEGER

User · Answer

A generic way to do    SELECT   FROM your table ORDER BY LENTH your column  ASC  your column ASC

User · Answer

cast REGEXP REPLACE NameNumber     0-9        as UNSIGNED

User · Answer

You can use SUBSTRING and CONVERT   SELECT stuff FROM table WHERE conditions ORDER BY CONVERT SUBSTRING name column  6   SIGNED INTEGER     Where name column is the column with the  name-  values  The SUBSTRING removes everything up before the sixth character  i e  the  name-  prefix  and then the CONVERT converts the left over to a real integer   UPDATE  Given the changing circumstances in the comments  i e  the prefix can be anything   you ll have to throw a LOCATE in the mix   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      name can be any sequence of letters   so that should be a safe assumption

User · Answer

To get number try with SUBSTRING INDEX field   -   1  then convert

User · Answer

This should work   SELECT field CONVERT SUBSTRING INDEX field  -  -1  UNSIGNED INTEGER  AS num FROM table ORDER BY num

User · Answer

SELECT    CAST SUBSTRING INDEX field   -   -1  AS UNSIGNED  as num FROM tableName ORDER BY num

[mysql] Convert text into number in MySQL query

Examples related to mysql

Examples related to sql