问题描述
我正在使用DB2 for IBM i V6R1,而我正在尝试将一个字符串值转换为有效数字的有效表示形式。我想出的是这样的:
I am using DB2 for IBM i V6R1, and I am trying to convert a string value which sometimes has a valid representation of a number in it into a number. What I came up with was this:
select onorno, onivrf, coalesce(cast(substr(onivrf,1,5) as numeric),99999) as fred
from oinvol
有时ONIVRF字段有数据像' 00111-11',有时它的数据就像FREIGHT。
sometimes the ONIVRF field has data like '00111-11', sometimes it has data like 'FREIGHT'.
文档让我相信这样的数据:
The documentation leads me to believe that for data like this:
ONORNO ONIVRF
12 11010-11
13 FREIGHT
14 00125-22
我应该得到这样的输出:
I should get output like this:
ONORNO ONIVRF FRED
12 11010-11 11010
13 FREIGHT 99999
14 00125-22 125
相反,我得到这个:
ONORNO ONIVRF FRED
12 11010-11 11010
13 FREIGHT NULL
14 00125-22 125
(如果我跳过 coalesce()
,只需使用 Cast(substr(onivrf(1,5))作为数字)
,我得到完全相同的结果。)
(If I skip the coalesce()
and just use the Cast(substr(onivrf(1,5) as numeric)
, I get exactly the same results.)
我在这里做错了什么?
推荐答案
如果你只是想摆脱所有字母字符的 ONIVRF
,你可以这样做:
If you're just trying to get rid of ONIVRF
s that are all alphabetic characters, you can do something like this:
SELECT ONORNO, ONIVRF,
CASE
WHEN UCASE(SUBSTR(ONIVRF,1,5)) = LCASE(SUBSTR(ONIVRF,1,5)) THEN CAST(SUBSTR(ONIVRF,1,5) AS NUMERIC)
ELSE 99999
END AS fred
FROM OINVOL
但字母字符是唯一由上下文函数翻译的字符。
It's a little hackish, because DB2 doesn't have a ISNUMERIC()
equivalent. But alphabetic characters are the only ones that will be translated by the up- and lower-case functions.
我在DB2 for z / OS(v9)上进行了测试,它有效,但我不知道DB2 for iSeries是否完全相同。对我来说,它是@Joe Stefanelli所说的,当它试图将一个字母串转换为 NUMERIC
时,引发了一个错误。
I tested this on DB2 for z/OS (v9), and it worked, but I'm not sure if DB2 for iSeries is exactly the same. On mine, it did as @Joe Stefanelli said, and raised an error when it tried to cast an alphabetic string to NUMERIC
.
编辑
这可能会更好(假设你不会有任何 ONIVRF
s都是波浪号)。它不应该有@ X-Zero提到的问题,除了英文以外的其他语言的字符没有大小写。
This might work better (assuming that you won't have any ONIVRF
s that are all tildes). It shouldn't have the problem that @X-Zero mentions where some characters in languages other than English don't have lower and upper-case.
SELECT ONORNO, ONIVRF,
CASE
WHEN TRANSLATE(ONIVRF, '~~~~~~~~~~~', '0123456789-') = '~~~~~~~~' THEN CAST(SUBSTR(ONIVRF,1,5) AS NUMERIC)
ELSE 99999
END AS fred
FROM OINVOL
这篇关于DB2 Coalesce函数返回null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!