DB2 ZOS 字符串比较问题
我正在比较 sql 中的 where 子句中的一些 CHAR 数据，如下所示，
其中 PRI_CODE < PriCode
我遇到的问题是 CHAR 值的长度不同。 因此，如果 PRI_CODE = '0800' 且 PriCode = '20'，则返回 true 而不是 false。
看起来它是这样比较的，
'08' < '20'
而不是像
'0800' < '20'
CHAR 比较从左侧开始直到一个或其他值结束吗？
如果是这样我该如何解决这个问题？
我的值可以包含字母，因此不能选择转换为数字。
I am comparing some CHAR data in a where clause in my sql like this,
where PRI_CODE < PriCode
The problem I am having is when the CHAR values are of different lengths.
So if PRI_CODE = '0800' and PriCode = '20' it is returning true instead of false.
It looks like it is comparing it like this
'08' < '20'
instead of like
'0800' < '20'
Does a CHAR comparison start from the Left until one or the other values end?
If so how do I fix this?
My values can have letters in it so convering to numeric is not an option.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
它不是将“08”与“20”进行比较，而是如您所料，将“0800”与“20”进行比较。
然而，您似乎没有想到的是，“0800”（字符串）确实小于“20”（字符串）。
如果无法将其转换为数字以进行数字比较，则可以使用以下 DB2 函数：
这将为您提供在左侧用零填充的
val，其大小为 10（对于例如，CHAR(10)）。这至少可以保证字段大小相同，并且比较适用于您的特定情况。但我敦促您重新思考您的做事方式：从性能角度来看，每行函数很少能很好地扩展。如果您使用 z/OS，您应该有一些 DBA 躺在计算机房的地板上等待工作 - 您可能可以向其中一位寻求更适合您的特定应用程序的建议
:-)请注意使用插入/更新触发器和辅助列
PRI_CODE_PADDED来保持完全填充的PRI_CODE列（使用与上面相同的方法）。然后，在执行select ... where PR_CODE_PADDED之前，确保您的。PriCode变量的格式类似。 PriCode在插入/更新时产生的成本将分摊到您可能执行的所有选择上（因为它们不再使用每行函数，所以速度将快得令人眼花缭乱），从而为您提供更好的整体性能（假设您的当然，数据库并不是那些写得比读得多的极其罕见的野兽之一）。
It's not comparing '08' with '20', it is, as you expect, comparing '0800' with '20'.
What you don't seem to expect, however, is that '0800' (the string) is indeed less than '20' (the string).
If converting it to numerics for a numeric comparison is out of the question, you could use the following DB2 function:
which will give you
valpadded on the left with zeroes to a size of 10 (ideal for aCHAR(10), for example). That will at least guarantee that the fields are the same size and the comparison will work for your particular case. But I urge you to rethink how you're doing things: per-row functions rarely scale well, performance-wise.If you're using z/OS, you should have a few DBAs just lying around on the computer room floor waiting for work - you can probably ask one of them for advice more tailored to your specific application :-)
One thing that comes to mind in the use of an insert/update trigger and secondary column
PRI_CODE_PADDEDto hold thePRI_CODEcolumn fully padded out (using the same method as above). Then make sure yourPriCodevariable is similarly formatted before executing theselect ... where PR_CODE_PADDED < PriCode.Incurring that cost at insert/update time will amortise it over all the selects you're likely to do (which, because they're no longer using per-row functions, will be blindingly fast), giving you better overall performance (assuming your database isn't one of those incredibly rare beasts that are written more than read, of course).