MySQLでアルファベット、数字の組み合わせを自然順でソートして表示したい

Question

###MySQLでアルファベット、数字の組み合わせを自然順でソートして表示したい

※情報が足りなかったため追記致します
MySQL初心者です。例えば以下のような

1
A1
B1
2
A3
A5
AA1
B4
A10

レコードが存在したとして、こちらを

1
2
A1
A3
A5
A10
AA1
B1
B4

といったようなソートで表示することは可能でしょうか？
現状では、

1
2
A1
A10
A3
A5
AA1
B1
B4

というソートになってしまいます。

Answer 1

ソートしたいデータは全て

[0文字以上のアルファベット] + [1文字以上の数字]

という形式で、0ab1やab23cdなどのようなデータは存在しない、という前提であれば、
以下の SQL文で意図通りに並ぶはずです。

SELECT * FROM test
ORDER BY
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') ASC,
  LENGTH(value) ASC,
  value ASC;

実行結果

mysql> CREATE TABLE test (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   value VARCHAR(8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (value) VALUES
    -> ('1'),
    -> ('A1'),
    -> ('B1'),
    -> ('2'),
    -> ('A3'),
    -> ('A5'),
    -> ('AA1'),
    -> ('B4'),
    -> ('A10'),
    -> ('A2');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test
    -> ORDER BY
    ->   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') ASC,
    ->   LENGTH(value) ASC,
    ->   value ASC;
+----+-------+
| id | value |
+----+-------+
|  1 | 1     |
|  4 | 2     |
|  2 | A1    |
| 10 | A2    |
|  5 | A3    |
|  6 | A5    |
|  9 | A10   |
|  7 | AA1   |
|  3 | B1    |
|  8 | B4    |
+----+-------+
10 rows in set (0.00 sec)

---

もっとも、初めからアルファベット部分と数字部分を別のカラムに格納しておけば
このように難しいことを考えずに済みます。

可能であれば、今からテーブル設計を変更することをお勧めします。

Comments

[doubutsu.karada]

ありがとうございました。今のところは[0文字以上のアルファベット] + [1文字以上の数字]のレコードしか存在しませんが、アドバイス頂いた通りテーブル設計の変更を検討してみます。

Answer 2

今は環境が無いので試せてませんが、こんな感じでどうでしょう？
ORDER BY 句だけです。
ORDER BY TRANSLATE(val, '0123456789', '') , LPAD(TRANSLATE(val, TRANSLATE(val, '0123456789', ''), ''), 10, '0')
アルファベット+数字の場合のみで数字は10桁までの対応です。

Comments

[KiyoshiMotoki]

MySQL に TRANSLATE という関数は存在しませんよ。

https://dev.mysql.com/doc/refman/5.6/ja/func-op-summary-ref.html
http://sqlfiddle.com/#!9/771552/1

[hihijiji]

おらおらとまちがえただ

Answer 3

普通にそうソートされると思いますが・・・

create table tbl(id int unique,val varchar(10));
insert into tbl values
(1,'1'),
(2,'A1'),
(3,'B1'),
(4,'2'),
(5,'A3'),
(6,'A5'),
(7,'AA1'),
(8,'B4');
select * from tbl order by val asc;

Comments

[doubutsu.karada]

すみません大切なところを書き忘れていました。
(9,'A10')のようなアルファベット＋数字二桁以上が入った場合に、

+------+------+
| id | val |
+------+------+
| 1 | 1 |
| 4 | 2 |
| 2 | A1 |
| 9 | A10 |
| 5 | A3 |
| 6 | A5 |
| 7 | AA1 |
| 3 | B1 |
| 8 | B4 |
+------+------+

のような表示結果になります。こちらを
A1
A3
A5
A10
といったソートで表示することは可能なのでしょうか？