CODEWITHSUNDEEP
mysqlsql
czuk
czuk

Reputation: 6408

Convert partially non-numeric text into number in MySQL query

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

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?

Upvotes: 159

Views: 615898

Answers (12)

dmishra
dmishra

Reputation: 1

I found it easier to use regex_replace function to strip off all non numeric values from the field and then sort.

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

Upvotes: 0

Gank
Gank

Reputation: 4667

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')),

Upvotes: -2

Jayram Kumar
Jayram Kumar

Reputation: 712

cast(REGEXP_REPLACE(NameNumber, '[^0-9]', '') as UNSIGNED)

Upvotes: 5

Azzu
Azzu

Reputation: 1

A generic way to do :

SELECT * FROM your_table ORDER BY LENTH(your_column) ASC, your_column ASC

Upvotes: -5

Sibin John Mattappallil
Sibin John Mattappallil

Reputation: 1789

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]

Upvotes: 29

V R K RAO
V R K RAO

Reputation: 111

one simple way SELECT '123'+ 0

Upvotes: 9

Harsha
Harsha

Reputation: 3578

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

Upvotes: 2

Marco
Marco

Reputation: 57593

This should work:

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

Upvotes: 286

mu is too short
mu is too short

Reputation: 434945

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.

Upvotes: 37

Gaurav
Gaurav

Reputation: 28775

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

Upvotes: 10

user744116
user744116

Reputation:

To get number try with SUBSTRING_INDEX(field, '-', 1) then convert.

Upvotes: 3

verdesmarald
verdesmarald

Reputation: 11866

You can use CAST() to convert from string to int. e.g. SELECT CAST('123' AS INTEGER);

Upvotes: 17

Related Questions