将SQL Server查询转换为DB2

Question

我正在尝试将以下SQL服务器查询转换为DB2或mysql…你能帮我一下吗?

SELECT 
*,
CASE 
WHEN PATINDEX('%S%LD%', orderstatus) > 0 
THEN 'Sold'
WHEN PATINDEX('%STOCK%', orderstatus) > 0 
OR PATINDEX('%STK%[0-9]/[0-9]%', orderstatus) > 0 
THEN 'Stock' 
ELSE'' 
END AS comment,
CASE 
WHEN PATINDEX('%[0-9]/[0-9]%', orderstatus) > 0 
OR CHARINDEX('*', orderstatus) > 0 
OR CHARINDEX('BAM', orderstatus) > 0
THEN 'BAM' 
ELSE'' 
END AS BAMYN,
CASE 
WHEN PATINDEX('%[0-9]/[0-9]%', orderstatus) > 0 
THEN CAST(SUBSTRING(orderstatus, CHARINDEX('/', orderstatus) - 2, 5) + '/2022' AS DATE)
ELSE orddate 
END AS soldorstockdate 
FROM 
input

想知道如何在DB2或mysql中替换CHARINDEX和PATINDEX?

查询尝试:

SELECT a.*, CASE WHEN REGEXP_COUNT(ORDERSTATUS,'SLD')>0 THEN 'Sold' 
WHEN REGEXP_COUNT ('%STOCK%',ORDERSTATUS)>0 OR REGEXP_COUNT ('STK[0-9]/[0-9]',ORDERSTATUS)>0 THEN 'Stock' ELSE'' END AS comment 
,CASE WHEN REGEXP_COUNT('[0-9]/[0-9]',ORDERSTATUS)>0 OR LOCATE('*',ORDERSTATUS)>0 OR LOCATE('BAM',ORDERSTATUS)>0
THEN 'BAM' ELSE'' END AS BAMYN 
,CASE WHEN REGEXP_COUNT('[0-9]/[0-9]',ORDERSTATUS)>0 
THEN CAST(SUBSTRING(ORDERSTATUS,LOCATE('/',ORDERSTATUS)-2,5) AS DATE)
ELSE ORDDATE  END AS soldorstockdate 

FROM input  a

我得到以下错误(AS 400)

SQL Error [2201S]: [SQ20558] REGEXP_COUNT函数的正则表达式字符串不合法

Answer 1

按原样运行以下命令。
如果返回错误的结果，那么提供一些相同形式的数据示例和期望的结果。

SELECT 
a.*
, CASE 
WHEN REGEXP_COUNT (ORDERSTATUS, 'S.*LD') <> 0 
THEN 'Sold' 
WHEN REGEXP_COUNT (ORDERSTATUS, 'STOCK|STK[0-9]{2}') <> 0 
THEN 'Stock' 
ELSE '' 
END AS comment 
, CASE 
WHEN REGEXP_COUNT (ORDERSTATUS, '[0-9]/[0-9]|*|BAM') <> 0
THEN 'BAM' 
ELSE '' 
END AS BAMYN 
, COALESCE (DATE (TO_DATE (REGEXP_SUBSTR (ORDERSTATUS, '[0-9]{2}/[0-9]{2}') || '/2022', 'MM/DD/YYYY')), CURRENT DATE)  
AS soldorstockdate 
FROM 
(
VALUES 'SooLD', 'STK12', 'STOCK', '1/2', 'BAM', 'ABC01/31DEF'
) A (ORDERSTATUS)

BAMYNtbody> <<tr>股票

ORDERSTATUS	COMMENT	SOLDORSTOCKDATE
SooLD	销售		2022-05-27
STK12	股票		2022-05-27
	股票		2022-05-27
1/2		BAM	2022-05-27
		BAM	2022-05-27
ABC01/31DEF		BAM	2022-01-31

在Db2-LUW中，最接近CHARINDEX的可能是LOCATE。

如果您的db2服务器平台和db2版本(您总是需要知道，z/os, i-series, linux/unix/windows/cloud)支持REGEXP_EXTRACT，您也可以使用REGEXP_EXTRACT。