SQL 拆分 DB2 中的 VARCHAR 以检索其中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1305804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split a VARCHAR in DB2 to retrieve a value inside
提问by Steve Schnepp
I have a VARCHARcolumn that contains 5 informations (2 CHAR(3)and 3 TIMESTAMP) separated with '$'.
我有一VARCHAR列包含 5 条信息（2CHAR(3)和 3 TIMESTAMP），以 ' $'分隔。
CREATE TABLE MYTABLE (
COL VARCHAR(256) NOT NULL
);
INSERT INTO MYTABLE
VALUES
( 'AAA'AAASELECT SPLIT(COL, '$', 4) FROM MYTABLE
1
-----
'null'
'null'
'2009-04-10 10:50:00'
009-10-10 10:50:00$null$null$null'
^^^^ this field
009-10-10 10:50:00$null$null$null' ),
( 'AAB007-04-10 10:50:00$null$null$null' ),
( 'AAC0$null07-04-10 10:50:0009-04-10 10:50:00$null' )
;
I would like to extract the 4th field ...
我想提取第四个字段...
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!
... to have something like
......有类似的东西
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;
I'm searching, in that order :
我正在搜索，按顺序：
- A DB2 build-in string function
- An embeddablestatement such as
SUBSTR(COL, POSSTR(COL)+1)... - An user defined function that behaves like
SPLIT
- DB2 内置字符串函数
- 一个可嵌入的语句，例如
SUBSTR(COL, POSSTR(COL)+1)... - 一个用户定义的函数，其行为类似于
SPLIT
Precision : Yes, I doknow that it's not a good idea to have such columns...
精度：是的，我不知道这是不是一个好主意，有这样的列...
采纳答案by Toni
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
Usage:
用法：
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
回答by nycjay
I am sure there is a better way to write this, but here is 1 (SQL) solution for the simple case given. It could be rewritten as a stored procedure to look for any arbitrary string. There may also be some 3rd party tools/extensions to help out w/ the split you want...
我相信有更好的方法来写这个，但这里是给定的简单案例的 1 (SQL) 解决方案。可以将其重写为存储过程以查找任意字符串。可能还有一些 3rd 方工具/扩展程序可以帮助解决您想要的拆分...
select
substring(col, LOCATE_IN_STRING(col, '$', 1, 3), LOCATE_IN_STRING(col, '$', 1, 4) - LOCATE_IN_STRING(col, '$', 1, 3))
from MYTABLE
回答by Jaruek R.
try this, it works!
试试这个，它有效！
substr(e.data,1,13) as NNSS,
substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 1, CODEUNITS32)+1, (LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 1, CODEUNITS32)-1) ) as Name,
substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32)+1, (LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 2, CODEUNITS32)-1) ) as Vorname,
substring(e.data, LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32)+1, (LOCATE_IN_STRING(e.data, ';', 1, 4, CODEUNITS32) - LOCATE_IN_STRING(e.data, ';', 1, 3, CODEUNITS32)-1) ) as Grund
回答by Esperento57
If your DB2's version can do it, you can use then LOCATE_IN_STRING function for to found position of your separator. The LOCATE_IN_STRING function returns the starting position of a string and enable you to choice the Nth instance. You can found documentation of this function here
如果您的 DB2 版本可以做到，那么您可以使用 LOCATE_IN_STRING 函数来找到分隔符的位置。LOCATE_IN_STRING 函数返回字符串的起始位置并使您能够选择第 N 个实例。您可以在此处找到此功能的文档
For your example, you can use this code :
对于您的示例，您可以使用以下代码：
##代码##