我有一个VARCHAR列,其中包含5个以’$’分隔的信息(2个CHAR(3)和3个TIMESTAMP).
CREATE TABLE MYTABLE (
COL VARCHAR(256) NOT NULL
);
INSERT INTO MYTABLE
VALUES
( 'AAA$000$2009-10-10 10:50:00$null$null$null' ),( 'AAB$020$2007-04-10 10:50:00$null$null$null' ),( 'AAC$780$null$2007-04-10 10:50:00$2009-04-10 10:50:00$null' )
;
我想提取第4场……
'AAA$000$2009-10-10 10:50:00$null$null$null'
^^^^ this field
……有类似的东西
SELECT SPLIT(COL,'$',4) FROM MYTABLE 1 ----- 'null' 'null' '2009-04-10 10:50:00'
我按顺序搜索：
> DB2内置字符串函数
>可嵌入的声明,如SUBSTR(COL,POSSTR(COL)1)……
>用户定义的函数,其行为类似于SPLIT
精度：是的,我知道拥有这样的专栏并不是一个好主意……
解决方法
CREATE FUNCTION split(pos INT,delimeter CHAR,string VARCHAR(255)) LANGUAGE sql RETURNS VARCHAR(255) DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC DECLARE x INT; DECLARE s INT; DECLARE e INT; SET x = 0; SET s = 0; SET e = 0; WHILE (x < pos) DO SET s = locate(delimeter,string,s + 1); IF s = 0 THEN RETURN NULL; END IF; SET x = x + 1; END WHILE; SET e = locate(delimeter,s + 1); IF s >= e THEN SET e = LENGTH(string) + 1; END IF; RETURN SUBSTR(string,s + 1,e - s -1); END!
用法：
SELECT split(3,col) from mytable; -- or SELECT split(0,'-','first-second-third') from sysibm.sysdummy1; SELECT split(0,'returns this') from sysibm.sysdummy1; SELECT split(1,'returns null') from sysibm.sysdummy1;
