选择不同起始位置的子字符串
选择不同起始位置的子字符串
提问于 2021-07-30 15:14:33
(我不确定我是否使用了正确的标题.如果你认为应该换别的东西，请告诉我)
嗨，我在尝试选择不同起始位置的子字符串。
我有张这样的桌子，
要求：
WITH
Req (COURSE_NUM, MAJOR) AS
(
VALUES
('A001', 'CS1 ')
, ('A002', 'CS2 CS1 CS3 CS4')
, ('A003', 'CS2 ')
, ('B001', 'CS3 CS1 ')
, ('B002', 'CS2 ')
)
SELECT * FROM ReqCOURSE_NUM MAJOR
---------- -------
A001 CS1
A002 CS2 CS1 CS3 CS4
A003 CS2
B001 CS3 CS1
B002 CS2 我想要一张这样的桌子：
MAJOR COURSE_NUM
----- -----------
CS1 A001
CS1 A002
CS1 B001
CS2 A002
CS2 A003
CS2 B002
CS3 A002
CS3 B002
CS4 A002搜索完之后，我发现CHARINDEX()可能是一个解决方案。因此，我尝试了DB2等效的LOCATE()：
Select SUBSTR(MAJOR, LOCATE('CS1', MAJOR), 3)
,COURSE_NUM
FROM Req
WHERE MAJOR LIKE '%CS1%'上面的查询将给出如下结果：
MAJOR COURSE_NUM
----- -----------
CS1 A001
CS1 A002
CS1 B001对一个少校来说没问题。如果我想要所有的专业怎么办？我还尝试在LOCATE()和LIKE子句中使用主机变量。但它提出了一个错误：
SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF
ARGUMENT 1 OF LOCATE IS INVALID 我不确定这个错误是由我的语法或其他限制引起的。
有什么办法或解决办法来实现这一点吗？如果你需要更多的信息，请告诉我。谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-07-30 19:59:49
LUW的Db2
WITH
Req (COURSE_NUM, MAJOR) AS
(
VALUES
('A001', 'CS1 ')
, ('A002', 'CS2 CS1 CS3 CS4')
, ('A003', 'CS2 ')
, ('B001', 'CS3 CS1 ')
, ('B002', 'CS2 ')
)
SELECT T.TOKEN, R.COURSE_NUM
FROM Req R, XMLTABLE
(
'for $id in tokenize($s, " +") return <i>{string($id)}</i>'
PASSING TRIM (R.MAJOR) as "s"
COLUMNS
TOKEN VARCHAR (10) PATH '.'
) T
ORDER BY T.TOKEN, R.COURSE_NUM;所有使用DB2函数的pratform的REGEXP_SUBSTR
WITH
Req (COURSE_NUM, MAJOR) AS
(
SELECT 'A001', 'CS1 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'A002', 'CS2 CS1 CS3 CS4' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'A003', 'CS2 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'B001', 'CS3 CS1 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'B002', 'CS2 ' FROM SYSIBM.SYSDUMMY1
)
, T (OCCURENCE, COURSE_NUM, MAJOR, TOKEN) AS
(
SELECT
1 AS OCCURENCE, COURSE_NUM, MAJOR AS MAJOR
, REGEXP_SUBSTR (MAJOR, '[^ ]+', 1, 1) TOKEN
FROM Req
UNION ALL
SELECT
OCCURENCE + 1, COURSE_NUM, MAJOR
, REGEXP_SUBSTR (MAJOR, '[^ ]+', 1, OCCURENCE + 1) TOKEN
FROM T
WHERE REGEXP_SUBSTR (MAJOR, '[^ ]+', 1, OCCURENCE + 1) IS NOT NULL
)
SELECT TOKEN, COURSE_NUM
FROM T
ORDER BY TOKEN, COURSE_NUM;使用LOCATE函数对所有pratform进行DB2
WITH
Req (COURSE_NUM, MAJOR) AS
(
SELECT 'A001', 'CS1 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'A002', 'CS2 CS1 CS3 CS4' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'A003', 'CS2 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'B001', 'CS3 CS1 ' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'B002', 'CS2 ' FROM SYSIBM.SYSDUMMY1
)
, T (COURSE_NUM, MAJOR, TOKEN) AS
(
SELECT
COURSE_NUM
, LTRIM (SUBSTR (MAJOR, NULLIF (LOCATE (' ', MAJOR), 0) + 1)) MAJOR
, SUBSTR (MAJOR, 1, COALESCE (NULLIF (LOCATE (' ', MAJOR), 0) - 1, LENGTH (MAJOR))) TOKEN
FROM (SELECT COURSE_NUM, TRIM (MAJOR) MAJOR FROM Req) R
UNION ALL
SELECT
COURSE_NUM
, LTRIM (SUBSTR (MAJOR, NULLIF (LOCATE (' ', MAJOR), 0) + 1)) MAJOR
, SUBSTR (MAJOR, 1, COALESCE (NULLIF (LOCATE (' ', MAJOR), 0) - 1, LENGTH (MAJOR))) TOKEN
FROM T
WHERE MAJOR IS NOT NULL
)
SELECT TOKEN, COURSE_NUM
FROM T
ORDER BY TOKEN, COURSE_NUM;Stack Overflow用户
发布于 2021-07-30 15:20:03
DB2有一个新的函数systools.split()来完成这个任务：
select req.course_num, element
from req r cross join
table(systools.split(major, ' ')) ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68593341
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号