我有一个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' )
;
我想提取第四场...
'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, string, 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;
我相信有一个更好的方式来写这篇文章，但这里是给定的简单情况1（SQL）解决方案。 它可以被改写为一个存储过程来查找任意字符串。 也可能有一些第三方工具/扩展，以帮助瓦特/你想分裂...
select
locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1) as poss3rdDollarSign, -- position of 3rd dollar sign
locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1) as poss4thDollarSign, -- position of 4th dollar sign
(locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) -
(locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1 as stringLength,-- length of string between 3rd and 4th dollar sign
substr(col, locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1) + 1, (locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) -
(locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1) as string
from mytable
试试这个，它的工作原理！
CREATE FUNCTION SPLIT( P_1 VARCHAR(3200),
P_2 VARCHAR(200))
RETURNS TABLE(P_LIST VARCHAR(3200))
SPECIFIC SPLIT
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
F1: BEGIN
return
with source(str, del) as
(select p_1, p_2 from sysibm.sysdummy1),
target(str, del) as
(select source.str, source.del from source
where length(source.str) > 0
union all
select
(case when (instr(target.str, target.del) > 0)
then substr(target.str,
instr(target.str, target.del)+1,
length(target.str)-instr(target.str, target.del)) else null end),
(case when (instr(target.str, target.del) > 0)
then target.del else null end)
from target
where length(target.str) > 0
)
select str from target
where str is not null;
END
如果DB2的版本可以做到这一点，你可以使用然后LOCATE_IN_STRING功能，以您的分离器的发现位置。 该LOCATE_IN_STRING函数返回一个字符串的起始位置，使您能够选择的N个实例。 你可以发现这个函数的文档在这里
对于你的榜样，您可以使用此代码：
select
substring(col, LOCATE_IN_STRING(col, '$', 1, 3), LOCATE_IN_STRING(col, '$', 1, 4) - LOCATE_IN_STRING(col, '$', 1, 3))
from MYTABLE
