简单实用的sql小技巧(第二篇)

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