SQL Oracle 查询以识别具有特殊字符的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25114268/
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
Oracle query to identify columns having special characters
提问by Ritisha Dang
I'm trying to write a SQL query to return rows which has anything other than
alphabets, numbers, spacesand following chars '.', '{','[','}',']'Column has alphabets like ?, ?
我试图写一个SQL查询返回的行具有比其他任何东西
alphabets，numbers，spaces和following chars '.', '{','[','}',']'列有字母一样?，?
eg:- There's a table TESTwith 2 columns - EmpNoand SampleTextEmpNois simple sequence and SampleTexthas values like
例如：- 有一个TEST包含 2 列的表-EmpNo并且SampleTextEmpNo是简单的序列并且SampleText具有类似的值
('12345abcde','abcdefghij','1234567890','ab c d 1 3','abcd$%1234','%^*&^%$#$%','% % $ # %','abcd 12}34{','MINNEA?POLIS','THAN ?VV ?A')
I want to write a query which should eliminate all rows which have even a single special character except .{[}]. In above example, it should return EmpNo - 1,2,3,4 and 8I tried REGEXP_LIKEbut I'm not getting exactly what I need.
我想编写一个查询，该查询应该消除除.{[}]. 在上面的例子中，它应该返回EmpNo - 1,2,3,4 and 8我试过REGEXP_LIKE但我没有得到我需要的东西。
Query I used:
我使用的查询：
SELECT * FROM test
WHERE REGEXP_LIKE(sampleText, '[^A-Z^a-z^0-9^[^.^{^}]' ,'x');
This is not ignoring blanks and I also need to ignore closing bracket ']'
这不是忽略空格，我还需要忽略右括号“]”
回答by Gordon Linoff
You can use regular expressions for this, so I think this is what you want:
您可以为此使用正则表达式，所以我认为这就是您想要的：
select t.*
from test t
where not regexp_like(sampletext, '.*[^a-zA-Z0-9 .{}\[\]].*')
回答by Ritisha Dang
I figured out the answer to above problem. Below query will return rows which have even a signle occurrence of characters besides alphabets, numbers, square brackets, curly brackets,s pace and dot. Please note that position of closing bracket ']' in matching pattern is important.
我想出了上述问题的答案。下面的查询将返回除了字母、数字、方括号、大括号、spacing 和 dot 之外，甚至出现单个字符的行。请注意，匹配模式中右括号“]”的位置很重要。
Right ']' has the special meaning of ending a character set definition. It wouldn't make any sense to end the set before you specified any members, so the way to indicate a literal right ']' inside square brackets is to put it immediately after the left '[' that starts the set definition
右边的 ']' 具有结束字符集定义的特殊含义。在指定任何成员之前结束集合是没有任何意义的，因此在方括号内指示文字右侧 ']' 的方法是将它紧跟在开始集合定义的左侧 '[' 之后
SELECT * FROM test WHERE REGEXP_LIKE(sampletext, '[^]^A-Z^a-z^0-9^[^.^{^}^ ]' );
回答by Gary_W
They key is the backslash escape character will not work with the right square bracket inside of the character class square brackets (it is interpreted as a literal backslash inside the character class square brackets). Add the right square bracket with an OR at the end like this:
它们的关键是反斜杠转义字符不能与字符类方括号内的右方括号一起使用（它被解释为字符类方括号内的文字反斜杠）。像这样在末尾添加带有 OR 的右方括号：
select EmpNo, SampleText
from test
where NOT regexp_like(SampleText, '[ A-Za-z0-9.{}[]|]');
回答by Raj Ratan
Compare the length using lengthB and length function in oracle.
使用oracle中的lengthB和length函数比较长度。
SELECT * FROM test WHERE length(sampletext) <> lengthb(sampletext)
SELECT * FROM test WHERE length(sampletext) <> lengthb(sampletext)
