替换 Oracle SQL 查询中的 TAB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23756440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replacing TAB in Oracle SQL query
提问by user3655976
Below is the query ,I am facing problem in replacing TAB and display into in one column
下面是查询，我在替换 TAB 并显示在一列中时遇到问题
with test as
(select 'ABC DEF GHI JKL MNO' str from dual
)
select regexp_substr (str, '[\t]+', 1, rownum) split
from test
connect by level <= length (regexp_replace (str, '[\t]+')) + 1
while query is working for comma in pace of tab [^,]
而查询正在以制表符 [^,] 的速度为逗号工作
回答by Frank Schmitt
You've got several problems in your query:
您的查询有几个问题：
- your
REGEXP_SUBSTRreturns the tabs instead of the words - you need to invert the character class - your row number calculation is wrong:
length (regexp_replace (str, '[\t]+')) + 1will return as many rows as the string contains tabs; uselength(str) - length(regexp_replace(...))instead
- 您
REGEXP_SUBSTR返回选项卡而不是单词 - 您需要反转字符类 - 您的行号计算错误：
length (regexp_replace (str, '[\t]+')) + 1将返回与字符串包含选项卡一样多的行；使用length(str) - length(regexp_replace(...))替代
Here's one version that uses [[:space:]]to match tabs (it will also match spaces etc.):
这是[[:space:]]用于匹配制表符的一个版本（它还将匹配空格等）：
with test as
(select 'ABC DEH G IJKL' str from dual
)
select str,
regexp_substr (str, '[^[:space:]]+', 1, rownum) split
from test
connect by level <= length(str) - length (regexp_replace (str, '[[:space:]]'))
The rownum calculation computes the number of whitespace characters by substracting the length of the "cleaned" string from the length of the original string
rownum 计算通过从原始字符串的长度中减去“清理过的”字符串的长度来计算空白字符的数量
回答by Art
I get expected result using Replace() fn. The thing is you must be abs sure your string is separated by tab and not smth else. First, I copied and separated string ABC and DEF... by tab in Notepad. Then I copied and run the query in SQL PLUS and get the correct output. Copy/Paste the example below in Notepad to see correct formatting. There is a tab between 'ABC DEF...':
我使用 Replace() fn 得到了预期的结果。问题是你必须绝对确定你的字符串是由制表符分隔的，而不是其他的。首先，我在记事本中通过选项卡复制并分隔字符串 ABC 和 DEF...。然后我在 SQL PLUS 中复制并运行查询并获得正确的输出。在记事本中复制/粘贴以下示例以查看正确的格式。'ABC DEF...' 之间有一个选项卡：
select REPLACE('ABC DEF GHI JKL MNO', chr(9), chr(32) ) str
from dual
/
STR
-------------------
ABC DEF GHI JKL MNO
