DB2 / iSeries SQL清理CR / LF，制表符等

问题描述

我需要在一组400k +字符串记录中查找并清理换行符，回车符，制表符和SUB字符，但这个DB2环境对我来说是一个骗局。

想想我可以用REPLACE（）和CHR（）函数进行一些搜索和替换，但似乎CHR（）在此系统上不可用（错误：* LIBL类型中的CHR *未找到）。使用\t，\r，\\\
等似乎也不起作用。字符串可以在字符串或其末尾。

  DBMS = DB2 
 System = iSeries 
语言= SQL 
编码=不确定，可能EBCDIC 
  

任何提示我可以做些什么？

解决方案

如果要清除特定字符，如回车（EBCDIC x'0d'）和换行符（EBCDIC x'25'），您应该在EBCDIC中找到，然后使用TRANSLATE（）函数用空格替换它们。

如果你只想删除不可显示的字符，那么在x'40'下找到任何东西。

I need to find and clean up line breaks, carriage returns, tabs and "SUB"-characters in a set of 400k+ string records, but this DB2 environment is taking a toll on me.

Thought I could do some search and replacing with the REPLACE() and CHR() functions, but it seems CHR() is not available on this system (Error: CHR in *LIBL type *N not found). Working with \t, \r, \n etc doesn't seem to be working either. The chars can be in the middle of strings or at the end of them.

DBMS = DB2
System = iSeries
Language = SQL
Encoding = Not sure, possibly EBCDIC

Any hints on what I can do with this?

解决方案

If you want to clear up specific characters like carriage return (EBCDIC x'0d') and line feed (EBCDIC x'25') you should find the translated character in EBCDIC then use the TRANSLATE() function to replace them with space.

If you just want to remove undisplayable characters then look for anything under x'40'.

这篇关于DB2 / iSeries SQL清理CR / LF，制表符等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！