简单实用的sql小技巧(第二篇)(r3笔记第86天)
公众号：杨建荣的学习笔记 · 作者：r3笔记第86天 · 发布：2014-12-16 22:27:32 · 原文链接
ASCII和CHR
在平时的工作中，可能会在sql或者pl/sql块中嵌入特殊字符，比如空格，回车之类。这个时候可以使用ascii和chr来做字符和ascii码的转换。
比如我们想得到字母a对应的ascii码,可以使用ascii来实现。
SQL> select ascii(‘a’) from dual;
ASCII(‘A’)
———-
97
如果反推，需要根据ascii码值来得到对应的字符，就可以使用chr
SQL> select chr(97) from dual;
CHR
—
a
关于ascii码对应的字符，总结的列表如下。对于前32个ascii码来说，是不可见字符。所以使用描述代替。这部分特殊字符在平时的工作中使用的地方还是比较多的。像回车符，换行符，空格都很常用。
用到的时候可以参考一下。
| ascii码 | 对应的字符 | ascii码 | 对应的字符 |
| 0 | null | 64 | @ |
| 1 | start of heading | 65 | A |
| 2 | start of text | 66 | B |
| 3 | end of text | 67 | C |
| 4 | end of transmission | 68 | D |
| 5 | enquiry | 69 | E |
| 6 | acknowledge | 70 | F |
| 7 | bell | 71 | G |
| 8 | backspace | 72 | H |
| 9 | horizontal tab | 73 | I |
| 10 | new line | 74 | J |
| 11 | vertical tab | 75 | K |
| 12 | new page | 76 | L |
| 13 | carriage return | 77 | M |
| 14 | shift out | 78 | N |
| 15 | shift in | 79 | O |
| 16 | data link escape | 80 | P |
| 17 | device control 1 | 81 | Q |
| 18 | device control 2 | 82 | R |
| 19 | device control 3 | 83 | S |
| 20 | device control 4 | 84 | T |
| 21 | negative acknowledge | 85 | U |
| 22 | synchronous idle | 86 | V |
| 23 | end of trans. block | 87 | W |
| 24 | cancel | 88 | X |
| 25 | end of medium | 89 | Y |
| 26 | substitute | 90 | Z |
| 27 | escape | 91 | [ |
| 28 | file separator | 92 | \ |
| 29 | group separator | 93 | ] |
| 30 | record separator | 94 | ^ |
| 31 | unit separator | 95 | _ |
| 32 | space | 96 | ` |
| 33 | ! | 97 | a |
| 34 | “ | 98 | b |
| 35 | # | 99 | c |
| 36 | $ | 100 | d |
| 37 | % | 101 | e |
| 38 | & | 102 | f |
| 39 | ‘ | 103 | g |
| 40 | ( | 104 | h |
| 41 | ) | 105 | i |
| 42 | * | 106 | j |
| 43 | + | 107 | k |
| 44 | , | 108 | l |
| 45 | – | 109 | m |
| 46 | . | 110 | n |
| 47 | / | 111 | o |
| 48 | 0 | 112 | p |
| 49 | 1 | 113 | q |
| 50 | 2 | 114 | r |
| 51 | 3 | 115 | s |
| 52 | 4 | 116 | t |
| 53 | 5 | 117 | u |
| 54 | 6 | 118 | v |
| 55 | 7 | 119 | w |
| 56 | 8 | 120 | x |
| 57 | 9 | 121 | y |
| 58 | : | 122 | z |
| 59 | ; | 123 | { |
| 60 | < | 124 | | |
| 61 | = | 125 | } |
| 62 | > | 126 | ~ |
| 63 | ? | 127 | DEL |
DUMP
对于一些特殊字符，有些是不可见字符，如果在客户端查看根本判断不出来。这个时候一旦出现什么问题，可以根据dump来查看是否存在特殊字符。举个简单的例子。
create table test(id number,name varchar2(100));
insert into test values(1,’this is a test’);
insert into test values(2,’this is a test’||chr(10));
commit;
SQL> col dump_name format a100
SQL> col name format a30
SQL> select *from test; –通过客户端查看，根本看不出区别来。一旦出现问题是很难查的。
ID NAME
———- ——————————
1 this is a test
2 this is a test
SQL> select id,dump(name)dump_name from test; -使用dump来解析，最后的”10“就是不可见字符，是一个换行符。
ID DUMP_NAME
———- —————————————————————————————————-
1 Typ=1 Len=14: 116,104,105,115,32,105,115,32,97,32,116,101,115,116
2 Typ=1 Len=15: 116,104,105,115,32,105,115,32,97,32,116,101,115,116,10
TO_CHAR (DATE)
关于to_char的使用，功能还是很强大的。看似简单的一个转换竟然能够实现很复杂的逻辑。
比如我们先得到当前的时间戳。
SQL> select to_char(sysdate,’yyyy–mm-dd hh24:mi:ss’) now_date from dual;
NOW_DATE
————————————————————
2014–12-16 17:46:39
得到这个月最后一天的信息，如果是在复杂的业务逻辑中判断能省去不少事。
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
——————
31-DEC-14
查看今天是这周的第几天。
SQL> select to_char(sysdate,’d’) from dual;
TO_
—
3
查看今天是星期几。
SQL> select to_char(sysdate,’day’) from dual;
TO_CHAR(SYSDATE,’DAY’)
—————————
tuesday
SQL> select to_char(sysdate,’dy’) from dual;
TO_CHAR(S
———
tue
查看今天是一年中的第几天
SQL> select to_char(sysdate,’ddd’) from dual;
TO_CHAR(S
———
350
查看今天是这个月的第几天
select to_char(sysdate,’dd’) from dual;
TO_CHA
——
16
查看今天是一年中的第几周
SQL> select to_char(sysdate,’ww’) from dual;
TO_CHA
——
50
查看月份
SQL> select to_char(sysdate,’MM’) from dual;
TO_CHA
——
12
select to_char(sysdate,’mm’) from dual;
TO_CHA
——
12
今天属于当月的第几周。
SQL> select to_char(sysdate,’W’) from dual;
TO_
—
3