我试图在 CAST(pid AS SIGNED)
的整数上对字符串的一部分进行排序,但没有成功。
我认为我需要这样的伪代码:CAST ON REGX('^islandora(/d?)$') OF pid AS SIGNED
doFields
表具有以下结构:
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| pid | varchar(64) | NO | MUL | NULL | |
| ownerId | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
这是示例数据:
+----------------+---------+
| pid | ownerId |
+----------------+---------+
| islandora:1050 | 8 |
| islandora:1052 | 8 |
| islandora:1053 | 8 |
| islandora:1054 | 8 |
| islandora:1055 | 8 |
+----------------+---------+
如何替换 islandora:
单词并将其施放为int。
SELECT *
FROM tableName
ORDER BY CAST(REPLACE(pid, 'islandora:', '') AS SIGNED) ASC
尝试以下:
order by
cast(substr(pid,locate(':',pid)+1) as SIGNED)
如果整数部分始终为4个字母长,则
Order by
cast(right(pid,4) as SIGNED)
尝试通过 -
使用此订单ORDER BY SUBSTRING_INDEX(pid, ':', -1) * 1;