CODEWITHSUNDEEP
mysqlsql-order-by
Mohammad Ali Akbari
Mohammad Ali Akbari

Reputation: 10395

Sort part of VARCHAR as INT in MySQL

I'm trying to sort on a part of string as integer by CAST(pid AS SIGNED) with no success.

I think I need something like this pseudocode: CAST ON REGX('^islandora(/d?)$') OF pid AS SIGNED

doFields table has following structure:

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| pid           | varchar(64)  | NO   | MUL | NULL    |       |
| ownerId       | varchar(255) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

here is sample data:

+----------------+---------+
| pid            | ownerId |
+----------------+---------+
| islandora:1050 | 8       |
| islandora:1052 | 8       |
| islandora:1053 | 8       |
| islandora:1054 | 8       |
| islandora:1055 | 8       |
+----------------+---------+

Upvotes: 0

Views: 107

Answers (3)

Devart
Devart

Reputation: 121902

Try to use this ORDER BY -

ORDER BY SUBSTRING_INDEX(pid, ':', -1) * 1;

Upvotes: 1

John Woo
John Woo

Reputation: 263693

how about replacing the word islandora: and casting it to int.

SELECT *
FROM tableName
ORDER BY CAST(REPLACE(pid, 'islandora:', '') AS SIGNED) ASC

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

order by 
       cast(substr(pid,locate(':',pid)+1) as SIGNED)

if the integer part is always 4 letter length then

  Order by
      cast(right(pid,4) as SIGNED)

Upvotes: 2

Related Questions