DB2字符函数简介及使用
DB2字符函数简介及使用
1、 ASCII(s)：取表达式s最左端一个字符的ascii码。
参数：任意内置字符类型，
返回值：integer类型。
db2 => values (ascii('A'),ascii('Abc'),ascii('1'),ascii('@'),ascii(null))
1 2 3 4 5
----------- ----------- ----------- ----------- -----------
65 65 49 64 -
1 条记录已选择。
2、 chr(n)：取ascii码n所代表的字符
参数：integer或者smallint类型，范围0-255
返回值：1个长度的字符。
db2 => values (chr(65),chr(49),chr(64),chr(35))
1 2 3 4
- - - -
A 1 @ #
1 条记录已选择。
3、 character_length(string,param2)(或者char_length(s,p))取字符串的长度
参数：string为任意字符串，或者graphic类型的字符串。
param2可以是codeunits16 — 16位的UTF-16编码，即string被表示为16位的UTF-16编码的字符串。
codeunits32 — 32位的UTF-32编码，即string被表示为32位的UTF-32编码的字符串。
octets — 认为是字节字符串。
返回值：返回string的存储的字符的实际长度。
db2 => select char_length(firstnme,octets),length(trim(firstnme)),firstnme from employee fetch first 5 rows only
1 2 FIRSTNME
----------- ----------- ------------
9 9 CHRISTINE
7 7 MICHAEL
5 5 SALLY
4 4 JOHN
6 6 IRVING
5 条记录已选择。
相关函数： length(str)，返回str的长度。
4、 concat(str1,str2)：连接字符串
参数：参数1和参数2可以是字符类型，数值类型，或者时间日期类型的数据。但是他们之间存在着兼容性，如下：
4.1 二进制字符串只能与二进制字符串连接
4.2 只有unicode编码的数据库才支持character字符串和graphic字符串相连，
在连接之前，character字符串会被转换为graphic字符串。
需要注意的是，被定义为FOR BIT DATA的character字符串是不能转换为graphic字符型的。
4.3 若参数不是字符串，那么在连接的过程中，将会被隐式的转换为字符串。
返回值：结果的长度和类型取决于在进行任何相互兼容的转换之后的类型和长度。
db2 => values (concat(sysdate,current time),concat(1,2)+10,concat(1,'q'),concat('chen_','CSDN'))
1 2 3 4
--------------------------- ------------------------------------------ -- ---------
2013-03-22-22.22.4422:22:44 22 1q chen_CSDN
1 条记录已选择。
字符串连接还可以使用符号||：
db2 => values (current date||' '||current time)
1
-------------------
2013-03-23 06:56:57
1 条记录已选择。
5、 lower(s)：将字符转换为小写
参数：参数必须是char或者varchar类型。
unicode编码的数据库中，支持graphic字符串，在改变为小写之前先转换为character字符串。
当参数为null的时候，返回值也为null。
返回值：字符或者字符串的小写形式，结果的类型与长度保持不变。
db2 => values (lower('U'),lower(null))
1 2
- -------------------------------------
u -
1 条记录已选择。
lcase()：作用于lower()函数一样。
l ower(locale sensitive)
lower(sysfun schema)
7、 upper(s)：将字符转换为大写
参数：char或者varchar字符型。
返回值：字符或者字符串的大写形式，结果的类型和长度保持不变。
db2 => values (upper('abc'),upper(null))
1 2
--- -------------------------------------
ABC -
1 条记录已选择。
相关函数：
ucase()：与upper()函数作用相同。
8、 translate(str,to,from[,pad])：将str中的from全部替换为to，若length(to)<length(from)，则用pad补齐。
参数：str：字符串
to：字符或者字符串
from：字符或者字符串
pad：字符或者字符串
返回值：返回被替换之后的字符串，需要注意的是，若是同音词，也会被替换。
若length(to)<length(from)，则结果中原来字符串from不足长度的地方使用空格补齐。
db2 => values (translate('text1 text2 test3 text4','*' ,'text','#'))
1
-----------------------
*##*1 *##*2 *#s*3 *##*4
1 条记录已选择。
db2 => values (translate('text1 text2 test3 text4','*' ,'text'))
1
-----------------------
* *1 * *2 * s*3 * *4
1 条记录已选择。
translate是一个功能强大的函数，更多信息： translate函数
相关函数：
replace(str,from,to)：将字符串str中的字符from全部替换为to。
若想将'text1 text2 test3 text4'中的所有空格都去掉：
db2 => values (replace('text1 text2 test3 text4',' ',''))
1
-----------------------
text1text2test3text4
1 条记录已选择。
9、 substr(str,start,len)
参数：str：可以为字符类型，数值类型，时间日期类型；
非字符串在进行截取之前，都被隐式的转换为varchar类型。
对于双字节字符（全角），将不返回任何值。
start：截取字符串的起始位置，integer类型，若非integer类型，将会隐式转换为integer类型。
大小在1和字符串的长度（定长）或者最大长度（变长）之间（正数）。
len：截取字符串的长度，integer类型，若非integer类型，将会隐式转换为integer类型。
注意：这三个参数可以其他函数生成。
返回值：字符类型。
db2 => values (substr('chenlinbo_csdn_china',11,4))
1
----
csdn
1 条记录已选择。
截取小数部分：
db2 => values (substr('100.101',locate('.',char(100.101))+1 ),100.101)
1 2
------- --------
101 100.101
1 条记录已选择。
截取小数的方法：http://www.csdn.net/bobo12082119/archive/
10、 substrb(str,start,len)：截取字符串长度
参数：str：同substr()函数；
start：若为正数，与substr()函数相同；
所为负数，则从字符串的末尾开始向字符串的开始位置处寻找截取的位置，
若绝对值大于字符串的长度，返回0长度的字符串。
若为0，则从str的起始位置1开始截取。
len：若非integer类型，则隐式自动转换为integer类型。
若len>length(str)，则截取的长度为：length(str) - start + 1。
若len<0，则len=0，即结果返回一个0长度的字符串。
返回值：返回值的类型为字符型，长度取决于len参数。
返回值的长度：
10.1 参数len<=0，返回值长度为0
10.2 若start不是常量，len是一个常量，结果的长度为：len和length(str)中的最小值。
10.3 若start为常量，len为指定，结果的长度为：length(str)-|start|。
若len为变量，则结果的长度为：length(str) - start + 1。
10.4 若start、len都为常量，结果的长度为：len和(length(str) - start + 1)中的最小值。
values (length(substrb('abcdef',-1,0)),
substrb('abcdef',-2,5),
length(substrb('abcdef',-2,-3)))
1 2 3
----------- -- -----------
0 ef 0
1 条记录已选择。
相关函数： substring
11、 ltrim(str)：去掉字符串前面的空格
参数：参数可以是字符型（char、varchar、graphic、vargraphic），数值型或时间日期型。
非字符数据，将被隐式转换为字符型。
返回值：若参数为char或者varchar类型，返回值为varchar类型。
若参数为vargraphic或者graphic，返回值为vargraphic类型。
若参数为多个空格，则返回长度为0的字符串，及空串。
db2 => values (ltrim(' abc ')||'-'||sysdate)
1
---------------------------
abc -2013-03-23-13.57.34
1 条记录已选择。
12、 rtrim(str)：去掉字符串末尾的空格
参数：参数可以是字符型（char、varchar、graphic、vargraphic），数值型或时间日期型。
非字符数据，将被隐式转换为字符型。
返回值：若参数为char或者varchar类型，返回值为varchar类型。
若参数为vargraphic或者graphic，返回值为vargraphic类型。
若参数为多个空格，则返回长度为0的字符串，及空串。
db2 => values (length(ltrim(' ')),rtrim(' abc ')||'cde')
1 2
----------- ----------
0 abccde
1 条记录已选择。
13、 trim(str):trim是一个功能比较强大的函数，能去掉字符串左右的空格或者多次出现的字符。
完整的语法如下：
>>-TRIM--(--+-----------------------------------------+--string-expression--)-><
| .-BOTH-----. |
'-+----------+--+-----------------+--FROM-'
+-B--------+ '-strip-character-'
+-LEADING--+
+-L--------+
+-TRAILING-+
'-T--------'
参数：
BOTH：从字符串的前后去掉指定字符，简写B。
LEADING：从字符串的最前端去掉指定字符，简写L。
TRAILING：从字符串的末尾去掉指定字符，简写T。
未指定这三个参数，默认为BOTH。
strip-character：去掉的单个字符；若此参数为指定，则默认为去掉字符串左或右的空格。
FROM str：参数可以是字符型（char、varchar、graphic、vargraphic），数值型或时间日期型。
非字符数据，将被隐式转换为字符型。
返回值：去掉指定字符或者空格的字符串。
去掉空格实例：
values (length(trim(' abc ')), --去掉空格之后，长度为3
'A'||trim(b from ' abc ')||'E', --去掉前后的空格
'A'||trim(t from ' abc '), --去掉尾部的空格
trim(l from ' abc ')||'D' --去掉前端的空格
)
1 2 3 4
----------- ------- ------ ------
3 AabcE A abc abc D
1 条记录已选择。
去掉重复出现的字符 — 简写(l,t,b)：
values (trim(l 'a' from 'abcd'), --
trim(t 'd' from 'abcd'), --
trim(b 'e' from 'eabcde')) --
1 2 3
---- ---- ------
bcd abc abcd
1 条记录已选择。
去掉重复出现的字符 — 全称(leading,trailing,both)
values (trim(leading 'a' from 'abcd'),
trim(trailing 'd' from 'abcd'),
trim(both 'e' from 'eabcde'))
1 2 3
---- ---- ------
bcd abc abcd
1 条记录已选择。
相关函数：
strip()：去除字符串前后空格或者多次出现的字符。
语法如下：
>>-STRIP--(--string-expression--+-----------------------------------------+--)-><
'-,--+-BOTH-----+--+--------------------+-'
+-B--------+ '-,--strip-character-'
+-LEADING--+
+-L--------+
+-TRAILING-+
'-T--------'
db2 => select salary,digits(salary),strip(digits(salary),b,'0') from employee fetch first 5 rows only
SALARY 2 3
----------- --------- ---------
152750.00 015275000 15275
94250.00 009425000 9425
98250.00 009825000 9825
80175.00 008017500 80175
72250.00 007225000 7225
5 条记录已选择。
14、 lpad(str,len,pad)：在字符串str的左侧使用pad字符(或字符串)补齐使其长度达到len，前提是str的长度小于len。
参数：str：可以是字符型（char、varchar、graphic、vargraphic），数值型或时间日期型。
若非字符串，在进行操作之前都将全部转换为字符串。
len：结果的长度，integer类型，character字符串长度的限制为32672，graphic字符串的限制为16336。
pad：补充的字符或者空格。
返回值：对于varchar类型的字符串，结果为右对齐的字符串，左边按照给定的字符补齐。
对于char类型的字符串，因为其长度已经固定，所以结果类似于在字符串的前面添加len-length(str)个pad。
若len<length(str)，则结果将是str的截取，substr(str,1,len)。
db2 => describe table employee
数据类型 列
列名 模式 数据类型名称 长 小数位 NULL
------------------------------- --------- ------------------- ---------- ----- ------
......
FIRSTNME SYSIBM VARCHAR 12 0 否
.....
JOB SYSIBM CHARACTER 8 0 是
......
14 条记录已选择。
select firstnme,lpad(firstnme,12,'.'),lpad(firstnme,5,'.'),lpad(job,10,'.')
from employee
fetch first 5 rows only
FIRSTNME 2 3 4
------------ ------------ ----- ----------
CHRISTINE ...CHRISTINE CHRIS ..PRES
MICHAEL .....MICHAEL MICHA ..MANAGER
SALLY .......SALLY SALLY ..MANAGER
JOHN ........JOHN .JOHN ..MANAGER
IRVING ......IRVING IRVIN ..MANAGER
5 条记录已选择。
15、 rpad(str,len,pad)：在字符串str的右侧使用pad字符(或字符串)补齐使其长度达到len，前提是str的长度小于len。
其他参数同lpad()函数，实例如下：
select firstnme,rpad(firstnme,12,'.'),rpad(firstnme,5,'.'),rpad(job,10,'.')
from employee
fetch first 5 rows onl
FIRSTNME 2 3 4
------------ ------------ ----- ----------
CHRISTINE CHRISTINE... CHRIS PRES ..
MICHAEL MICHAEL..... MICHA MANAGER ..
SALLY SALLY....... SALLY MANAGER ..
JOHN JOHN........ JOHN. MANAGER ..
IRVING IRVING...... IRVIN MANAGER ..
5 条记录已选择。
16、 locate(str,Source,start[,strunit]):查找Source字符串中字符(或字符串)str，在start位置后首次出现的位置。
参数：str：字符或者字符串。
source：字符串
start：integer类型，必须大于0，默认值为1。
strunit：★CODEUNITS16, CODEUNITS32, or OCTETS★，此参数非必选。
返回值：integer类型，str首次在source中从start位置开始出现的位置。
未找到，则返回0。
若str和source其中一个为null的话，返回值也为null。
若str是长度为0的字符串，则返回1。
若source的长度为0，则返回0。
实例：
db2 => select cid,coalesce(cname,'UNKNOW'),sex,age from test1
CID 2 SEX AGE
---------- -------------------- --- -----------
1 scott M 30
002 chenlinbo M 20
003 F 21
004 UNKNOW F 32
4 条记录已选择。
select coalesce(cname,'UNKNOW'), --等同于nvl(cname,'UNKNOW')，去部位null的那个参数
--等同于value(str1,str2)，返回不为空的字符串
locate('o',cname,1), --返回o首次出现的位置，从位置1开始，若sname为null，则返回0
locate('',cname,1), --若str是长度为0的字符串，则返回1
locate(null,cname,1) --str、source其中一个为null，则返回null
from test1
1 2 3 4
-------------------- ----------- ----------- -----------
scott 3 1 -
chenlinbo 9 1 -
0 1 -
UNKNOW - - -
4 条记录已选择。
相关函数： locate_in_string(str,source,start,strunit)：同locate()函数，不同点就是支持start小于0。
instr()是locate_in_string()函数的别名。
17、 position(str,source,strunit):查找str字符(或字符串)在source字符串中首次出现的位置。
strunit参数必须有：CODEUNITS16, CODEUNITS32, or OCTETS。
参数如上，与locate()函数不同的它不可以指定查找的其实位置，默认从字符串的起始位置查找。
db2 => select position('co',cname),cname from test1
SQL0440N 找不到具有兼容自变量的类型为 "FUNCTION" 的名为 "POSITION"
的已授权例程。 SQLSTATE=42884
db2 => select position('co',cname,octets),cname from test1
1 CNAME
----------- --------------------
2 scott
0 chenlinbo
0
- -4 条记录已选择。
相关函数：posstr(source,str)
18、 left(str,len[,strunit])：截取字符串str左端的len个长度的字符。
参数：
str：字符类型，定长或者边长，graphic字符串(双字节字符串)。
非字符型，在进行截取之前都转换为变长字符串。
len：截取的长度，
若len=0，则返回一个长度为0的字符串;
若len<0，则报错，超出了范围；
若len>length(str)，则取整个字符串str。
strunit：非必须参数，可为CODEUNITS16, CODEUNITS32, or OCTETS。
返回值：返回截取的字符串，若str为null，则返回null，其他参数可视为无效。
db2 => values (left('abcdef',-3))
SQL0138N 内置字符串函数的数字自变量超出了范围。 SQLSTATE=22011
db2 => select cname,left(cname,5),left(cname,21) from test1
CNAME 2 3
-------------------- -------------------- ---------------------
scott scott scott
chenlinbo chenl chenlinbo
- - -
4 条记录已选择。
19、 right(str,len,strunit)：截取str右端的len个长度的字符串。
参数：
str：字符类型，定长或者边长，graphic字符串(双字节字符串)。
非字符型，在进行截取之前都转换为变长字符串。
len：截取的长度，
若Len<0，报错，内置字符串函数的数字自变量超出了范围。
若len=0，返回一个0长度的字符串。
若len>length(str)，则取str字符串。
strunit：非必须参数，可为CODEUNITS16, CODEUNITS32, or OCTETS。
返回值：若str为null，则返回null，可视其他参数无效。
db2 => select cname,right(cname,3),right(cname,21) from test1
CNAME 2 3
-------------------- -------------------- ---------------------
scott ott scott
chenlinbo nbo chenlinbo
- - -
4 条记录已选择。
20、 space(n)：返回n个空格，n为integer或者smallint类型。
db2 => values (length(space(10)))
1
-----------
10
1 条记录已选择。
21、 coalesce(str1,str2)：返回2个字符串中不为空的那一个
功能相同的还有: nvl(str1,str2)， value(str1,str2)
22、 nvl2(str1,str2,str3)
此函数可以使用case表达式来表示，如下：
CASE WHEN str1 IS NOT NULL
THEN ste2
ELSE str3
END
db2 => select cname,nvl2(cname,cname,'UNKNOW') from test1
CNAME 2
-------------------- --------------------
scott scott
chenlinbo chenlinbo
- UNKNOW
4 条记录已选择。
23、 initcap(str)：将str字符串的首字母大写。
db2 => values (initcap('i''m a boy'),initcap('SCOTT'),initcap('scott'),initcap('___yeeXun'),initcap('890csdn'))
1 2 3 4 5
--------- ----- ----- --------- -------
I'M A Boy Scott Scott ___Yeexun 890csdn
1 条记录已选择。
更多信息，参照： DB2内置函数
--the ned--