Sybase:如何使用 SQL 从 CHAR 或 VARCHAR 字段中删除不可打印的字符?
我正在使用一个 Sybase 数据库,该数据库的某些字符串字段中似乎包含不可打印的字符,这导致我们的一些处理代码失效。乍一看,它似乎只是换行符和回车符,但其中还有一个 ASCII 代码 27 - 一个 ESC 字符、一些重音字符和其他一些奇怪的字符。
我无法直接访问更改数据库,因此还无法更改错误数据。现在我只能把它过滤掉。我们正在尝试从一个数据库导出表数据,并将其加载到每晚批处理过程中另一个应用程序使用的数据库中。
理想情况下,我希望有一个函数可以传递字符列表,然后让 Sybase 返回删除了这些字符的数据。如果可能的话,我想保留一些我们可以用普通 SQL 做的事情。
像这样删除 ASCII 0 - 31 的字符。
select str_replace(FIELD1, (0-31), NULL) as FIELD1, str_replace(FIELD2, (0-31), NULL) 作为FIELD2 到目前为止
,str_replace 是我能找到的最接近的,但它只允许用一个字符串替换另一个字符串。不支持字符范围,也不会让我执行上述操作。
我们在 Unix 服务器上的 Sybase ASE 12.5 上运行。
I'm working with a Sybase database that seems to have non-printable characters in some of the string fields and this is throwing off some of our processing code. At first glance, it seemed to only be newlines and carriage returns, but we also have an ASCII code 27 in there - an ESC character, some accented characters, and some other oddities in there.
I have no direct access to change the database, so changing the bad data isn't an option, yet. For now I have to make do with just filtering it out. We're trying to export the table data from one database and load it into a database used by another application in a nightly batch process.
Ideally, I'd like to have a function that I can pass a list of characters and just have Sybase return the data with those characters removed. I'd like to keep it something we could do in plain SQL if possible.
Something like this to remove characters that are ASCII 0 - 31.
select str_replace(FIELD1, (0-31), NULL) as FIELD1,
str_replace(FIELD2, (0-31), NULL) as FIELD2
from TABLE
So far, str_replace is the nearest I can find, but it only allows replacing one string with another. No support for character ranges and won't let me do the above.
We're running on Sybase ASE 12.5 on Unix servers.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
类似的事情在 SQL Server 中有效,它像 Sybase 一样使用 T-SQL:
patindex 函数 至少在 Sybase 上似乎存在。
排序规则需要匹配二进制;否则
[a]
将匹配 'á'。Something like this works in SQL Server, which uses T-SQL like Sybase:
The patindex function at least appears to exist on Sybase.
The collation is required to match binary; otherwise
[a]
would match 'á'.