工作闲暇之余,整理了AS400上的DB2内置函数,下面是字符函数。
CHAR(exp1 , exp2):将表达式转换为字符串。
返回值取决于第一个参数:①时间,日期,时间戳的字符串形式,exp1为时间,日期,时间戳。
②字符串,exp1为任何形式的字符串。
③整数的字符串形式,exp1为SMALLINT,INTEGER,BIGINT。
④十进制数的字符串形式,exp1为PACKED or ZONED十进制数。
⑤双精度浮点数,exp1为DOUBLE or REAL。
对于TIME和DATE类型有下面的格式:
Date Type | Format |
ISO | yyyy-mm-dd |
USA | mm/dd/yyyy |
EUR | dd.mm.yyyy |
JIS | yyyy-mm-dd |
LOCAL |
|
时间戳的格式为:yyyy-mm-dd-hh:mm:ss.milsec
截取字符串:CHAR(‘ABCDEFGHIJK’,6)=’ABCDEF’
替代字符:CHAR(123.45,‘,’)=123,45
--替换字符串,截取字符串
SELECT CHAR(123.456,'m') ,CHAR('ABCDEFGHIJK',6)FROM SYSIBM.SYSDUMMY1
1 2
123m456 ABCDEF
--日期格式
SELECT char(date('10/25/2011'),iso) COL1,
char(current date,usa) COL2,
char(date('10/25/2011'),eur) COL3,
char(date('10/25/2011'),jis) COL4,
char(date('10/25/2011'),local) COL5
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....+.
COL1 COL2 COL3 COL4 COL5
2011-10-25 08/25/2012 25.10.2011 2011-10-25 10/25/11
VARCHAR(exp1,exp2,exp3):返回值
1. 字符串:第一个参数为任意类型的字符串。
2. Graphic String:第一个参数为UCS-2 graphic 字符串。
3. 整数:第一个参数是smallint,integer,bigint。
4. 十进制数:第一个参数为packed或者zoned十进制数。
5. 双精度浮点数:第一个参数为double或者real类型的数。
--character to varchar
VARCHAR(exp1,length[DEFAULT],integer):
Exp1:定长字符串,变长字符串,CLOB类型。
Length:返回字符串的长度。
如果length大于字符串长度,则返回值为原字符串。
如果length小雨字符串长度,则从字符串首部开始截取长度为length的字符串。
DEFAULT:如果exp1为null,返回值为1,否则返回整个字符串。
Integer:指定结果的CCSID值,必须为一个有效的单字节字符SBCS CCSID[返回值为单字节字符],mixed data CCSID[返回值为mixed数据],或者65535[位数据]。如果此参数为SBCS CCSID,那么exp1不能为DBCS-either或者DBCS-only字符串。
SELECT LENGTH(VARCHAR('ABC',5))
FROM QSYS2/QSQPTABL
....+....1....
LENGTH
3
--截取字符串
SELECT VARCHAR('ABCDEFGH',5) FROM QSYS2/QSQPTABL
....+..
VARCHAR
ABCDE
--
SELECT LENGTH(VARCHAR('ABCD ')) FROM QSYS2/QSQPTABL
....+....1....
LENGTH
7
--DEFAULT关键字
SELECT VARCHAR('ABCDEFGH',DEFAULT)FROM QSYS2/QSQPTABL
....+....1....+.
VARCHAR function
ABCDEFGH
--
SELECT VARCHAR('ABCDEFG',2,1)
FROM QSYS2/QSQPTABL
Character conversion between CCSID 836 and CCSID 1 not valid.
--整型数据转换为变长字符串
--十进制数据转换为变长字符串
--浮点数转换为变长字符串
CCSID信息:CCSID
CHAR,VARCHAR函数都是功能很强大的函数,具体参考:DB2内置函数
CONCAT(parm1,parm2):字符串连接,||功能相同。
--DB2里面连接字符串只能用CONCAT函数,或者“||”,不能使用“+”。
SELECT CONCAT(CAST(CURDATE() AS CHAR(12)),
CAST(CURTIME() AS CHAR(12)))
FROM QSYS2/QSQPTABL
....+....1....+....2....
CONCAT
2012-08-26 18:12:46
---
SELECT CONCAT(CHAR(CURDATE()),CHAR(CURTIME())) COL1,'aa'||23 COL2
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3...
COL1 COL2
2012-08-2618:15:45 aa23
CHAR_LENGTH(string),CHARACTER_LENGTH(string),LENGTH(string):获取字符串长度,包括空格。
SELECT CHARACTER_LENGTH('ABCDEFG'),
CHAR_LENGTH(' 123456 '),
LENGTH(' 0123 ')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+...
CHARACTER_LENGTH CHAR_LENGTH LENGTH
7 8 7
Current connection is to relational database I5DEV.
SELECT CURRENT DATE,CURRENT TIME,CURRENT TIMESTAMP
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....
CURRENT DATE CURRENT TIME CURRENT TIMESTAMP
2012-08-24 08:50:51 2012-08-24-08.50.51.461345
--测试
SELECT char_length('123654 '),character_length('abcde'), length('123456789 ')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+...
CHAR_LENGTH CHARACTER_LENGTH LENGTH
9 5 10
DIFFERENCE(parm1,parm2):返回2个字符串的类似度,4(最相似),3,2,1(相似度最低)。
SELECT DIFFERENCE('TREES','TRACE') COL1
,DIFFERENCE('TREE','TRACE') COL2
,DIFFERENCE('TRES','TRACE') COL3
,DIFFERENCE('APPLE','TRACE') COL4
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....+....6..
COL1 COL2 COL3 COL4
4 3 4 1
SOUNDEX(parm):返回4个用来分析单词的发音字符值;参数是除了BLOB类型之外的任意长度字符串。
SELECT DIFFERENCE('CONSTRAINT','CONTRITE'),
SOUNDEX('CONSTRAINT'),
SOUNDEX('CONTRITE')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3..
DIFFERENCE SOUNDEX SOUNDEX
2 C523 C536
DIGITS(parm):类似CHAR函数,将数字转换为数字。数字(or 字符数字) —>数值。
SELECT DIGITS(-10.12)+10 FROM QSYS2/QSQPTABL
....+....1....+...
Numeric Expression
1,022
----
SELECT DIGITS('123.10'),LENGTH(DIGITS('123.10')) FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....
DIGITS ( '123.10' ) LENGTH
000000000000000000000000000001231000000000000000000000000000000 63
SUBSTR(string , from , to):截取字符串。从string的from位置开始截取字符串,截取长度为to,包括from位置的字符,结果为字符串。
SELECT SUBSTR('DB2-For-System-i',4,3)
FROM QSYS2/QSQPTABL
....+.
SUBSTR
-Fo
--
SELECT SUBSTR('DB2-For-System-i',LENGTH('DB2-For-System-i'),3)
FROM QSYS2/QSQPTABL
....+.
SUBSTR
i
--
SELECT COALESCE(SUBSTR('DB2',0,1),'EE') COL1,
LENGTH(SUBSTR('DB2',-1,2)) COL2,
SUBSTR('DB2',-1,3) COL3,
LENGTH(SUBSTR('DB2',-3,4)) COL4,
SUBSTR('DB2',-1,5) COL5
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....
COL1 COL2 COL3 COL4 COL5
2 D 4 DB2
--
SELECT LENGTH(SUBSTR('ABCDEF',-1,0))
FROMQSYS2/QSQPTABL
....+....1....
LENGTH
0
INSERT(parm1,parm2,parm3,parm4):在源字符串里面添加字符串。
注意:第一个参数的起始位置为1。
INSERT(‘ABCDEF’,3,4,’OUT’)= ‘ABOUT’
第一个参数:源字符串
第二个参数:添加字符串的起始位置
第三个参数:从源字符串中删除的字符串
第四个参数:添加到源字符串里面的字符串;若为 null,则从源字符串中从起始位置截去长度为参数三的串。SELECT INSERT('ABCDEFGH',1,0,'XXX') COL1, --直接字符串首添加
INSERT('ABCDEFGH',1,2,'XXXXX') COL2,--删除前2个字符,在添加字符串
INSERT('ABCDEFGH',8,0,'ZZ') COL3 --在第8个字符后面添加字符串
FROM SYS2/QSQPTABL
....+....1....+....2....+....3....+.
COL1 COL2 COL3
XXXABCDEFGH XXXXXCDEFGH ABCDEFGZZH
-----
SELECT INSERT('ABCD',4,2,'....'), --删除第4个字符后面的2个字符(包括末尾),再添加字符串
INSERT('ABCD',5,1,'999999') --在字符串末尾(length + 1)添加
FROM QSYS2/QSQPTABL
....+....1....+....
INSERT INSERT
ABC.... ABCD999999
GRAPHIC,VARGRAPHIC(parm):将字符或者数字转换为与双字节字符数据相容的值,如中文和日文。即,将单字节字符转换为双字节字符:
SELECT GRAPHIC('ABC'),VARGRAPHIC('WWW') FROM QSYS2/QSQPTABL
....+....1....+....2....+....
GRAPHIC ( 'ABC' ) VARGRAPHIC
ABC WWW
LCASE,LOWER(parm):将字符转换为小写;
UCASE,UPPER(parm):将字符转换为大写;
LEFT(srcStr,len):从字符串的左面开始,截取字符串。
LEFT(‘ABC123’,3)= ‘ABC’
RIGHT(srcStr,len):从字符串的右面开始,截取字符串。
RIGHT(‘ABC123’,3)= ‘123’SELECT LEFT('_yeeXun',3),RIGHT('_yeeXun',4)FROM QSYS2/QSQPTABL
....+....1....+.
LEFT RIGHT
_ye eXun
SUBSTR( or SUBSTRING)(srcStr,Strart,len):截取字符串。
SUBSTR(‘ABC123’,3,2)= ‘C1’
LOCATE(str,srcStr,start_position):在源字符串中从指定位置开始查找子字符串首次出现的实际位置。
LOCATE(‘AB’,’ABCABCABE’)= 1
LOCATE(‘AB’,’ABCABDABE’,3)= 2
SELECT LOCATE('AB','ABCABEABD'),--默认从字符串头位置1开始
LOCATE('AB','ABCABEABD',2), -- 返回字符串首次出现的实际位置
LOCATE('AB','ABCABEABD',LENGTH('ABCABEABD')) --没找到则返回0
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+.
LOCATE LOCATE LOCATE
1 4 0
POSSTR,POSITION(subStr,srcStr):获取子字符串在源字符串中 首次出现的 实际位置。
SELECT POSSTR('ABCABEABD','E'),
POSITION('12' IN 'ABCD12EF12JK')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3...
POSSTR POSITION function
6 5
LTRIM(parm):去掉字符串前面的空格
RTRIM(parm):去掉字符串后面的空格
TRIM:从字符串中删除指定的字符串,功能更加强大
SELECT LTRIM(' 000'),
RTRIM('111 '),
TRIM(' ABC '),
TRIM('1' FROM 'ASD1345D1111111'),
TRIM('10' FROM '10.203410101010.'), --不可以截取多个字符串
TRIM(LEADING '0' FROM '000123000'),
TRIM(TRAILING '0' FROM '000123000'),
TRIM(BOTH '0' FROM '000123000'),
TRIM('0' FROM '000123000')
FROM QSYS2/QSQPTABL
--
SELECT LTRIM(' 000'),
RTRIM('111 '),
TRIM(' ABC '),
TRIM('1' FROM 'ASD1345D1111111')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+..
LTRIM ( ' 000' ) RTRIM ( '111 ' ) TRIM ( ' ABC ' ) TRIM function
000 111 ABC ASD1345D
--
SELECT TRIM(LEADING '0' FROM '000123000'),
TRIM(TRAILING '0' FROM '000123000'),
TRIM(BOTH '0' FROM '000123000'),
TRIM('0' FROM '000123000')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....5....+...
TRIM function TRIM function TRIM function TRIM function
123000 000123 123 123
REPEAT(srcStr , parm2):重复字符串。
SPACE(parm):返回参数个空格。
SELECT REPEAT('123.',2) FROM QSYS2/QSQPTABL
....+...
REPEAT
123.123.
----
SELECT LENGTH(SPACE(10)) FROM QSYS2/QSQPTABL
....+....1....
LENGTH
10
-----
SELECT'AAA'||SPACE(10)||'BB',LENGTH(CONCAT('CC ',SPACE(10))),
LENGTH('AAA'||SPACE(10)||'BB')
FROM QSYS2/QSQPTABL
....+....1....+....2....+....3....+....4....+....
String Expression LENGTH LENGTH
AAA BB 13 15
REPLACE(subStr , srcStr , reStr):将第二个参数中的第一个参数出现的位置替换为第三个参数。
REPLACE(‘XY’ , ‘XYZ’, ‘BI’) = ‘BIZ’
REPLACE(‘XY’ , ‘XYZ’, ‘’) = ‘Z’
REPLACE(‘XY’ , ‘XYZ’ , ‘JAZ’) = ‘JAZZ’
REPLACE(‘XY’ , ‘ABC’ , ‘DE’) = ‘ABC’
COALESCE(exp1, exp2,exp3):返回同类型的参数中,第一个不为空(NULL)的参数,常用与字符串的比较。
SELECT COALESCE(STRCOL,'优快云','BLOG'),NUMCOL,DTECOL
FROM YTCCLB/TSTTB
....+....1....+....2....+....3....+....4
COALESCE NUMCOL DTECOL
优快云 10 -
_yeeXun - 2012-08-28
NULLIF(exp1,exp2):若exp1=exp2则返回NULL,否则返回exp1;参数exp1,exp2必须为相同类型或者兼容,比如字符串和时间类型可兼容。
其作用等同于CASEexp1=exp2 THEN NULL ELSE exp1 END。
IFNULL(exp1,exp2):如果exp1为NULL,返回exp2,否则返回exp1。--创建表
CREATE TABLE YTCCLB/TSTTB (
STRCOL VARCHAR ( 12) NOT NULL WITH DEFAULT,
NUMCOL INT NOT NULL WITH DEFAULT,
DTECOL DATE NOT NULLWITH DEFAULT)
Table TSTTB in YTCCLB created but was not journaled.
--添加数据失败
INSERT INTO YTCCLB/TSTTB VALUES(NULL, 10, NULL)
Null values not allowed in column or variable STRCOL.
--修改表
ALTER TABLE YTCCLB/TSTTB ALTER COLUMN STRCOL DROP NOT NULL
ALTER completed for table TSTTB in YTCCLB.
ALTER TABLE YTCCLB/TSTTB ALTER COLUMN NUMCOL DROP NOT NULL
ALTER completed for table TSTTB in YTCCLB.
ALTER TABLE YTCCLB/TSTTB ALTER COLUMN DTECOL DROP NOT NULL
ALTER completed for table TSTTB in YTCCLB.
--添加数据成功
INSERT INTO YTCCLB/TSTTB VALUES(NULL, 10, NULL)
1 rows inserted in TSTTB in YTCCLB.
--测试NULLIF,IFNULL函数:
SELECT IFNULL(NULLIF(STRCOL,'_yeeXun'),'DB2'),
NUMCOL,IFNULL(DTECOL,'2012-01-01')
FROM YTCCLB/TSTTB
SELECT statement run complete.
....+....1....+....2....+....3....+....4
IFNULL NUMCOL IFNULL
DB2 10 2012-01-01
--查看YTCCLB/TSTTB表中的数据,空值以“-”表示:
SELECT * FROM YTCCLB/TSTTB
....+....1....+....2....+....3....+....4
STRCOL NUMCOL DTECOL
- 10 -
_yeeXun - 2012-08-28
更多DB2 400内置函数简介,参考: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmstscale.htm