Convert partially non-numeric 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 "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?

name is exactly name or it can be any character? I mean: is it a string four chars long or is a real name? — Marco
– Marco, Commented May 11, 2011 at 7:26

Marco · Accepted Answer · 2012-04-05 10:25:22Z

286

This should work:

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

edited Apr 5, 2012 at 10:25

answered May 11, 2011 at 7:28

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Angelo Fuchs Over a year ago

could you add an explanation and a link to the documentation?

Eduardo Over a year ago

My string is like "name-abc12". By adding your code, it only works if the initial characters after "-" not starts with a letter. @Marco Can you tell me a way to ignore the letters without a where condition?

Marco Over a year ago

@Eduardo my query is supposed to get the string after the "-" and convert it into a number (it MUST be a number). In your case I'd go on using a regular expression probably...

Anchith Acharya Over a year ago

More info on this command

perepm Over a year ago

@Eduardo Marco is using SUBSTRING_INDEX which uses a given separator (- in this case) to split the string in "columns" and return the portion of that separation that you ask for (with the third argument). All details in the documentation: dev.mysql.com/doc/refman/5.7/en/…

mu is too short · Accepted Answer · 2011-05-11 07:51:51Z

37

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.

edited May 11, 2011 at 7:51

answered May 11, 2011 at 7:27

mu is too short

436k71 gold badges863 silver badges822 bronze badges

2 Comments

Marco Over a year ago

Answering my comment, he told us name can be any sequence of chars, so I'm not sure you can use SUBSTRING(name_column, 6). I know, you posted it when he didn't tell us this...

mu is too short Over a year ago

@Marco: Thanks for the heads up, I added an update that should take care of the new information about the prefixes. But yeah, your SUBSTRING_INDEX is nicer.

Sibin John Mattappallil · Accepted Answer · 2016-11-16 05:23:17Z

29

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]

answered Nov 16, 2016 at 5:23

Sibin John Mattappallil

1,7894 gold badges27 silver badges38 bronze badges

Comments

verdesmarald · Accepted Answer · 2011-05-11 07:25:59Z

17

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

answered May 11, 2011 at 7:25

verdesmarald

11.9k2 gold badges46 silver badges60 bronze badges

1 Comment

Hobo Over a year ago

Is that version specific? I need to use SELECT CAST('123' AS SIGNED INTEGER); or SELECT CAST('123' AS UNSIGNED INTEGER); to get it to work.

Gaurav · Accepted Answer · 2011-05-11 07:38:04Z

10

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

edited May 11, 2011 at 7:38

answered May 11, 2011 at 7:29

Gaurav

28.8k9 gold badges54 silver badges81 bronze badges

1 Comment

Marco Over a year ago

Are you sure that the ORDER BY uses num as a number without using CONVERT? I'm not sure, but it can be.. I'm just asking myself :)

V R K RAO · Accepted Answer · 2016-07-04 10:43:12Z

9

one simple way SELECT '123'+ 0

edited Jul 4, 2016 at 10:43

answered Jul 4, 2016 at 6:45

V R K RAO

1111 silver badge4 bronze badges

3 Comments

Toby Speight Over a year ago

Although this code may be help to solve the problem, providing additional context regarding why and/or how it answers the question would significantly improve its long-term value. Please edit your answer to add some explanation.

Sagar Shah Over a year ago

This did not answer the question but it was the answer i was looking for.

chukko Over a year ago

your solution is the most elegant and practical - unfortunately you didnt provide it in the context of the question with specific expression for given example - please modify it to be specific.

Jayram Kumar · Accepted Answer · 2020-01-11 10:43:36Z

5

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

answered Jan 11, 2020 at 10:43

Jayram Kumar

7224 gold badges17 silver badges32 bronze badges

2 Comments

prashant Over a year ago

Exactly what I want :)

Jayram Kumar Over a year ago

It's my fortune, I came out helpful for you.

user744116 · Accepted Answer · 2011-05-11 07:26:36Z

3

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

answered May 11, 2011 at 7:26

user744116

1 Comment

Gaurav Over a year ago

You need to change 1 to -1. dev.mysql.com/doc/refman/5.0/en/…

Harsha · Accepted Answer · 2013-07-15 11:25:23Z

2

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

answered Jul 15, 2013 at 11:25

Harsha

3,60821 gold badges55 silver badges76 bronze badges

Comments

Obsidian · Accepted Answer · 2021-07-10 00:03:46Z

0

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;

edited Jul 10, 2021 at 0:03

Obsidian

3,9308 gold badges20 silver badges33 bronze badges

answered Jul 9, 2021 at 14:23

dmishra

1

Comments

Gank · Accepted Answer · 2021-03-09 03:19:51Z

-2

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

answered Mar 9, 2021 at 3:19

Gank

4,6974 gold badges52 silver badges49 bronze badges

2 Comments

Nico Haase Over a year ago

Please add some explanation to your answer such that others can learn from it

Jeff Clayton Over a year ago

This one may just be responding to the title of this post - converting text to number (in this case a text uuid string) -- using replace (or pretty much anything else) is generally faster than a regex. This also suggests the idea of using a uuid string as primary key. A plain old autoincrement or perhaps a binary uuid would be faster than conversion to string if that is the goal in this case.

Azzu · Accepted Answer · 2017-04-13 14:46:33Z

-5

A generic way to do :

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

answered Apr 13, 2017 at 14:46

Azzu

1

Collectives™ on Stack Overflow

Convert partially non-numeric text into number in MySQL query

12 Answers 12

5 Comments

2 Comments

Comments

1 Comment

1 Comment

3 Comments

2 Comments

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

5 Comments

2 Comments

Comments

1 Comment

1 Comment

3 Comments

2 Comments

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related