REGEXP_LIKE中的CHR(0) [英] CHR(0) in REGEXP_LIKE

问题描述

我正在使用查询来检查chr(0)在regexp_like中的行为.

I am using the queries to check how chr(0) behaves in regexp_like.

CREATE TABLE t1(a char(10));
INSERT INTO t1 VALUES('0123456789');
SELECT CASE WHEN REGEXP_LIKE(a,CHR(0)) THEN 1 ELSE 0 END col, DUMP(a)  
FROM t1;

我得到的输出是这样-

col                     dump(a)
-----------             -----------------------------------
 1                     Typ=96 Len=10: 48,49,50,51,52,53,54,55,56,57

我完全感到困惑，如果没有如dump(a)所示的chr(0)，regexp_like如何在列中找到chr(0)并返回1?它不应该在这里返回0吗?

I am totally confused, if there is no chr(0) as shown by the dump(a), how regexp_like is finding the chr(0) in the column and returning 1? Shouldn't it return 0 here?

推荐答案

CHR(0)是用于终止使用C编程语言(除其他外)的字符串的字符.

CHR(0) is the character used to terminate a string in the C programming language (among others).

当您将CHR(0)传递给函数时，它将依次将其传递给较低级的函数，该函数将解析您传入的字符串并从该字符串构建正则表达式模式.此正则表达式模式将看到CHR(0)并认为它是字符串终止符，而忽略该模式的其余部分.

When you pass CHR(0) to the function it will, in turn, pass it to lower level function that will parse the strings you have passed in and build a regular expression pattern from that string. This regular expression pattern will see CHR(0) and think it is the string terminator and ignore the rest of the pattern.

使用REGEXP_REPLACE可以更轻松地查看行为:

The behaviour is easier to see with REGEXP_REPLACE:

SELECT REGEXP_REPLACE( 'abc' || CHR(0) || 'e', CHR(0), 'd' )
FROM   DUAL;

运行此命令会发生什么:

What happens when you run this:

• CHR(0)被编译为正则表达式并成为字符串终止符.
• 现在该模式只是字符串终止符，因此该模式是长度为零的字符串.
• 然后将正则表达式与输入字符串进行匹配，并读取第一个字符a，并找到可以在a之前匹配的零长度字符串，因此它将替换在a之前已匹配的内容. d给出输出da.
• 然后重复下一个将b转换为db的字符.
• ，依此类推，直到到达字符串末尾为止，此时它将匹配零长度模式并附加最后一个d.

• CHR(0) is compiled into a regular expression and become a string terminator.
• Now the pattern is just the string terminator and so the pattern is a zero-length string.
• The regular expression is then matched against the input string and it reads the first character a and finds a zero-length string can be matched before the a so it replaces the nothing it has matched before the a with an d giving the output da.
• It will then repeat for the next character transforming b to db.
• and so on until you reach the end-of-string when it will match the zero-length pattern and append a final d.

您将获得输出:

dadbdcd_ded

(其中_是CHR(0)字符.)

注意:输入中的CHR(0)不会被替换.

Note: the CHR(0) in the input is not replaced.

如果您正在使用的客户端程序也在CHR(0)处截断字符串，您可能看不到整个输出(这是客户端表示字符串的方式，而不是Oracle的输出的问题)，但也可以使用DUMP()显示:

If the client program you are using is also truncating the string at CHR(0) you may not see the entire output (this is an issue with how your client is representing the string and not with Oracle's output) but it can also be shown using DUMP():

SELECT DUMP( REGEXP_REPLACE( 'abc' || CHR(0) || 'e', CHR(0), 'd' ) )
FROM DUAL;

输出:

Typ=1 Len=11: 100,97,100,98,100,99,100,0,100,101,100

[TL; DR] 那么

REGEXP_LIKE( '1234567890', CHR(0) )

它将创建一个长度为零的字符串正则表达式模式，并在1字符之前寻找一个长度为零的匹配项-它将找到该字符，然后返回找到一个匹配项.

It will make a zero-length string regular expression pattern and it will look for a zero-length match before the 1 character - which it will find and then return that it has found a match.

这篇关于REGEXP_LIKE中的CHR(0)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！