为什么 Oracle 吃掉了我的字符串？
我目前尝试在 Oracle DB 上执行以下查询，
select tzname || ' (UTC'|| tz_offset(tzname) || ')' from v$timezone_names
它似乎不是很复杂。只是大括号中的时区名称和 UTC 偏移量。但是当我在 Windows 上使用 PL/SQL Developer 执行查询时，它总是会吃掉最后一个大括号。
所以我去了 sqlplus 并在那里执行它，现在我得到了最后一个大括号，但在最后一个大括号之前还有一个额外的空格作为额外的好处。
我已经尝试使用嵌套的 to_char() 和 trim() 但没有任何变化。我也在不同的数据库上尝试过，但它总是一样的。
有人知道 tz_offset 和字符串连接是否有问题吗？
i currently try to execute the following query on an Oracle DB
select tzname || ' (UTC'|| tz_offset(tzname) || ')' from v$timezone_names
It not seems to be very complicated. Just the name of the timzone and the UTC offset in braces. But when i execute the query with PL/SQL Developer on windows it always eats up the last brace.
So I went to sqlplus and executed it there and now i get my last brace but also an additional whitespace before the last brace as an extra goody.
I've tried it with nested to_char() and trim() but nothing changes. I also tried it on different DBs but it's always the same.
Does anybody know if there is a problem with tz_offset and string concatenation?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
发出以下查询：
您将得到如下结果：
这表明 tz_offset() 返回以 null 结尾的字符串（可能是一个错误）。因此，对于您的查询，Oracle 将返回
考虑到这一点，我猜想 PL/SQL Developer 将 \0 解释为字符串结尾（可能是另一个错误，SQL 字符串不是以 null 终止的），因此它不需要编写字符串的其余部分，因此您失去了尾部大括号。 SQL*PLus 选择打印空白而不是空值，然后继续处理字符串的其余部分，打印右大括号。
作为解决方法，您可以将
tz_offset(...)替换为replace(tz_offset(...), chr(0))。这将从tz_offset(...)返回的任何内容中删除空值。Issuing the following query:
You get results like these:
This shows that
tz_offset()returns null-terminated strings (maybe a bug). So for your query, Oracle is returningHaving that in mind, I guess that PL/SQL Developer interprets \0 as end-of-string (maybe another bug, SQL strings are not null-terminated) and so it does not bother writing the rest of the string, so you lose the trailing brace. SQL*PLus chooses instead to print a whitespace instead of that null and then proceeds with the rest of the string, printing the closing brace.
As a workaround, you can replace
tz_offset(...)withreplace(tz_offset(...), chr(0)). This will delete nulls from whatevertz_offset(...)returns.它适用于子字符串，但这并不能真正回答你的问题为什么会发生:-)：
select tzname || ' (UTC'|| substr(tz_offset(tzname),1,6) || ')' 来自 v$timezone_names;
It works with substring but that doesn't really answer your question why it is happening :-):
select tzname || ' (UTC'|| substr(tz_offset(tzname),1,6) || ')' from v$timezone_names;
我正在创建一个 JSON ajax 资源，它返回需要包含时区偏移量的时间戳...那个尾随控制字符真的很烦我，我按如下方式修剪：
regexp_replace(tz_offset('Canada/Mountain'),'[[ :cntrl:]]','')
I was creating a JSON ajax resource that returns timestamps that need to include the timezone offset... that trailing control character was really annoying me, I trim if off as follows:
regexp_replace(tz_offset('Canada/Mountain'),'[[:cntrl:]]','')