Oracle数据库:如何在字符串的特定位置添加空格 [英] Oracle db: How to add spaces in specific positions in strings

问题描述

假设我有一个字符串(varchar2)，并且我想在有两个连续的a的地方添加一个空格.例如:'graanh' -> 'gra anh'.

Suppose I have a string (varchar2) and I want to add a space wherever I have two consecutive a's. So for example: 'graanh' -> 'gra anh'.

好，用replace或regexp_replace这样做很简单.但是两者都会阻塞三个或更多连续的a.例如:

OK, this is trivial to do, either with replace or regexp_replace. But both choke on three or more consecutive a's. For example:

SQL> select replace('aaaaa', 'aa', 'a a') from dual;

REPLACE
-------
a aa aa

SQL> select regexp_replace('aaaaa', 'aa', 'a a') from dual;

REGEXP_
-------
a aa aa

这是因为搜索匹配模式的下一个"出现是在上一个"结束之后开始的.实际上，regexp_count('aaaaa', 'aa')返回2，而不是4.

This is because the search for the "next" occurrence of the match pattern begins after the end of the "previous" one. Indeed, regexp_count('aaaaa', 'aa') returns 2, not 4.

所需的结果是'aaaaa' -> 'a a a a a'.

真正的问题是我不想替换模式'aa'.我要替换的是在空字符串两侧的空字符串.因此，描述将类似于:找到所有出现在字符串两边都带有a的空字符串，然后用空格替换该空字符串.

The problem, really, is that I don't want to replace the pattern 'aa'. What I do want to replace is the empty string BETWEEN a's on both sides of the empty string. So the description would be something like: Find all occurrences of an empty string with a's on both sides of it, and replace that empty string with a space.

这可以通过字符串或正则表达式函数来完成吗?请不提供具有递归查询的解决方案，或者提供(令人恐惧的)PL/SQL过程；我知道他们可以做到，但是我对是否有一个自然的"解决方案(正是针对这个问题而存在的一种工具)感兴趣，我很想念.

Can this be done with string or regexp functions? Please do not offer solutions with recursive queries, or (the horror!) PL/SQL procedures; I know they can be done, but I am interested in whether there is a "natural" solution, a tool that exists precisely for this question, that I am missing.

最坏的情况是replace(replace('aaaaa', 'aa', 'a a'), 'aa', 'a a')可以解决问题.这也是最佳案例吗?

Worst case, replace(replace('aaaaa', 'aa', 'a a'), 'aa', 'a a') will do the trick. Is this the best case as well?

免得您认为这是一个奇怪的要求:我正在尝试使用多种方法来分隔逗号分隔的字符串.有些方法非常讨厌忽略这样的字符串中的NULL标记(即，从两个连续的逗号中得到的不是您应该得到的NULL).因此，在使用这些方法之前，必须先解析输入的字符串并在连续的逗号之间添加空格或字符串"NULL"或其他内容.当然，最好在每个逗号之后(在字符串的开头)添加一个空格，然后将其拆分为令牌，以从每个令牌的开头删除一个空格；但这引出了我上面提出的问题，我认为这是值得的.

Lest you think this is a weird requirement: I am experimenting with different methods of splitting comma-separated strings. Some of the methods have a very nasty habit of ignoring NULL tokens within such strings (that is, from two consecutive commas you don't get a NULL, as you should). So, before using those methods, one would have to parse the input string and add a space, or the string 'NULL', or something, between consecutive commas. Of course, it would be better to add a space after EVERY comma (and at the beginning of the string), and after splitting into tokens, to remove a space from the beginning of each token; but this raised the question I asked above, which I think has merit on its own.

谢谢！

编辑(2020年4月13日)，自我首次发布此问题以来，我对正则表达式有了很多了解.现在我知道了这个术语. (我发现我给出的解释是正确的，只是我并没有使用技术术语.)在这里，我们需要的环视 strong>-并且Oracle正则表达式不支持它们. END EDIT

EDIT (13 April 2020) I learned a lot about regular expressions since I first posted this question. Now I know the terminology for this. (I find that the explanation I gave is correct, I just wasn't using technical terms for it.) What we need here, to be able to add spaces everywhere they are needed in a single pass, is lookarounds - and Oracle regular expressions don't support them. END EDIT

推荐答案

如果您确实发现PL/SQL 恐怖和递归查询不自然，那么您可能必须使用replace(replace('aaaaa', 'aa', 'a a'), 'aa', 'a a')，因为您已经提出了建议，尽管是最坏的情况".为什么我认为这是最糟糕的情况?

If you truly find PL/SQL the horror and recursive queries unnatural, then you probably have to go with replace(replace('aaaaa', 'aa', 'a a'), 'aa', 'a a'), as you already have proposed, albeit as "worst case". Why you consider this the worst case remains unclear to me.

这篇关于Oracle数据库:如何在字符串的特定位置添加空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！