Powerful SQL: SQL Cookbook Reading Notes 1-Sort data with mixed letters and numbers

Powerful SQL: SQL Cookbook Reading Notes 1-sorting the mixed data of letters and numbers recently, I am reading a really good book on SQL Cookbook. Many solutions are very subtle, I really realized that SQL is powerful. Note: I use ORACLE 11g and Below is an example in book 2.4 -- Sort the data sequence of mixed letters and numbers. First, we need a table emp in the book, the table creation file or statement is not provided in the book. I am using an ORACLE database. I create an emp table based on the data in the book, create a table, and save the data. below is the SQL statement for data insertion. You are blessed, do not input one by one. PS: the dump file originally intended to be uploaded to the emp table, but the upload restrictions apply. [SQL] <span style = "font-size: 18px;"> insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'Smith ', 'cler', 7902, '17-DEC-1980', 800, null, 20); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'allen ', 'salesman', 7698, '20-FEB-1981', 1600,300, 30); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'Ward ', 'salesman', 7698, '20-FEB-1981', 1250,500, 30); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'Jones ', 'manager', 7839, '02-APR-1981', 2975, null, 20); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'martin ', 'salesman', 7698, '28-SEP-1981', 1250,140 0, 30 ); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, C OMM, DEPTNO) values (7698, 'bucke', 'manager', 7839, '01-MAY-1981 ', 2850, null, 30); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'clark', 'manager', 7839, '09-JUN-1981 ', 2450, null, 10); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'Scott ', 'analyst', 7566, '09-DEC-1982 ', 3000, null, 20); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'King', 'President ', null, '17-NOV-1981', 5000, null, 10 ); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'turner ', 'salesman', 7698, '08-SEP-1981 ', 1500, 0, 30); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'adams', 'cler ', 7788, '12-JAN-1983 ', 1100, null, 20); insert Into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'James ', 'cler', 7698, '03-DEC-1981 ', 950, null, 30); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'Ford ', 'analyst ', 7566, '03-DEC-1981 ', 3000, null, 20); insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'miller', 'wheel', 7782, '23-JAN-1982 ', 130 0, null, 10); </span> Create a table, insert data, and create a view. The statement is as follows: [SQL] <span style = "font-size: 18px; "> create view v as select ename |'' | deptno as data from emp </span>. Then, the created VIEW v contains letters or numbers, how can I sort query results? 1. the number is separated and the query results are sorted by the number (DEPTNO). It is verified by the ORACLE database and feasible. The SQL statement is as follows: [SQL] <span style = "font-size: 18px; "> select data from v order by replace (DATA, REPLACE (TRANSLATE (DATA, '000000 ','##########'),'#', ''),''); </span> 2. the results are sorted by letters (ENAME) and verified by the ORACLE database. The SQL statements are as follows: [SQL] <span style = "font-size: 18px; "> select data from v order by replace (TRANSLATE (DATA, '000000 ','##########'),'#',''); </span> when I look at it, I feel the words separated. Mother, there is a space next to it, so I saw the query statement of the separation VIEW in the book, and confirmed that my idea is correct, the query statement is as follows: [SQL] <span style = "font-size: 18px;"> SELECT DATA, REPLACE (DATA, REPLACE (TRANSLATE (DATA, '123 ', ########## '),' # ','), ') NUMS, REPLACE (TRANSLATE (DATA, '123 ', ########## '),' # ',') chars from v; </span> copies the value of CHARS in the separately queried field, paste it. Multiple spaces are displayed. SQL, so powerful, learned, haha.