时间戳

首页 > 解决方案 > 选择具有不同起始位置的子字符串

sql - 选择具有不同起始位置的子字符串

问题描述

（我不确定我是否使用了正确的标题...如果您认为应该将其更改为其他内容，请告诉我）

嗨，我正在尝试选择具有不同起始位置的子字符串。

我有一张这样的桌子，

要求：

WITH 
  Req (COURSE_NUM, MAJOR) AS
(
VALUES
  ('A001', 'CS1            ')  
, ('A002', 'CS2 CS1 CS3 CS4')
, ('A003', 'CS2            ')
, ('B001', 'CS3 CS1        ')  
, ('B002', 'CS2            ')
)
SELECT * FROM Req

COURSE_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

我不确定错误是由我的语法或其他限​​制引起的。

有什么方法或解决方法可以做到这一点？如果您需要更多信息，请告诉我。谢谢！

标签： sqldb2

解决方案

用于 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 for all pratforms 使用 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;

DB2 for all pratforms 使用 LOCATE 函数

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;

推荐阅读