I'm trying to write a SQL query to return rows which has anything other than
alphabets
, numbers
, spaces
and following chars '.', '{','[','}',']'
Column has alphabets like Ÿ
, ¿
eg:- There's a table TEST
with 2 columns - EmpNo
and SampleText
EmpNo
is simple sequence and SampleText
has values like
('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 8
I tried REGEXP_LIKE
but I'm not getting exactly what I need.
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 ']'
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 .{}\[\]].*')
Compare the length using lengthB and length function in oracle.
SELECT * FROM test WHERE length(sampletext) <> lengthb(sampletext)
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:
select EmpNo, SampleText
from test
where NOT regexp_like(SampleText, '[ A-Za-z0-9.{}[]|]');
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.
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^[^.^{^}^ ]' );
Source: Stackoverflow.com