在默认的环境中,DB2中的NULL字段显式的是'-',有时候需要把它和单字符'-'区分,有时候需要把它显示为空。本文提供了一些方法和思路
例如,下面的例子中,第一行的name为NULL,显示的为'-':
inst97@db2a:~$ db2 "create table t1(id int, name varchar(20))"
inst97@db2a:~$ db2 "insert into t1 values(1001, NULL)"
inst97@db2a:~$ db2 "insert into t1 values(1002, '')"
inst97@db2a:~$ db2 "insert into t1 values(1003, '-')"
inst97@db2a:~$ db2 "select * from t1"
ID NAME
----------- --------------------
1001 -
1002
1003 -
3 record(s) selected.
为了和单字符'-'区分,可以使用length()函数查看字段长度,对于NULL,长度仍为NULL。对于空串,长度为0,对于'-',长度为1:
inst97@db2a:~$ db2 "select id, name, length(name) as namelength from t1"
ID NAME NAMELENGTH
----------- -------------------- -----------
1001 - -
1002 0
1003 - 1
3 record(s) selected.
若要把NULL字段显示为空,可以使用select语句的case功能,把NULL字段都显示为空,样例如下:
inst97@db2a:~$ db2 "select id, case when name is NULL then '' else name end as name from t1"
ID NAME
----------- --------------------
1001
1002
1003 -
3 record(s) selected.
上面的做法仅仅是针对单条SQL语句,如果表/SQL很多,要统一显示NULL字段为空,可以考虑使用clpplus:
inst97@db2a:~$ clpplus inst97/inst97@localhost:60001/sample -nw
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664 SQL0907B
SQL authorization ID = inst97
Local database alias = SAMPLE
Port = 60001
SQL> select * from t1;
ID NAME
----------- --------------------
1001
1002
1003 -
SQL> exit;
如果要使用脚本,可以按照下面的方式处理:
inst97@db2a:~$ cat select.sh
select * from t1;
exit;
inst97@db2a:~$ clpplus inst97/inst97@localhost:60001/sample -nw @select.sh
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664 SQL0907B
SQL authorization ID = inst97
Local database alias = SAMPLE
Port = 60001
ID NAME
----------- --------------------
1001
1002
1003 -
例如,下面的例子中,第一行的name为NULL,显示的为'-':
inst97@db2a:~$ db2 "create table t1(id int, name varchar(20))"
inst97@db2a:~$ db2 "insert into t1 values(1001, NULL)"
inst97@db2a:~$ db2 "insert into t1 values(1002, '')"
inst97@db2a:~$ db2 "insert into t1 values(1003, '-')"
inst97@db2a:~$ db2 "select * from t1"
ID NAME
----------- --------------------
1001 -
1002
1003 -
3 record(s) selected.
为了和单字符'-'区分,可以使用length()函数查看字段长度,对于NULL,长度仍为NULL。对于空串,长度为0,对于'-',长度为1:
inst97@db2a:~$ db2 "select id, name, length(name) as namelength from t1"
ID NAME NAMELENGTH
----------- -------------------- -----------
1001 - -
1002 0
1003 - 1
3 record(s) selected.
若要把NULL字段显示为空,可以使用select语句的case功能,把NULL字段都显示为空,样例如下:
inst97@db2a:~$ db2 "select id, case when name is NULL then '' else name end as name from t1"
ID NAME
----------- --------------------
1001
1002
1003 -
3 record(s) selected.
上面的做法仅仅是针对单条SQL语句,如果表/SQL很多,要统一显示NULL字段为空,可以考虑使用clpplus:
inst97@db2a:~$ clpplus inst97/inst97@localhost:60001/sample -nw
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664 SQL0907B
SQL authorization ID = inst97
Local database alias = SAMPLE
Port = 60001
SQL> select * from t1;
ID NAME
----------- --------------------
1001
1002
1003 -
SQL> exit;
如果要使用脚本,可以按照下面的方式处理:
inst97@db2a:~$ cat select.sh
select * from t1;
exit;
inst97@db2a:~$ clpplus inst97/inst97@localhost:60001/sample -nw @select.sh
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved.
Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664 SQL0907B
SQL authorization ID = inst97
Local database alias = SAMPLE
Port = 60001
ID NAME
----------- --------------------
1001
1002
1003 -