Oracle query to identify columns having special characters

Question

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       amp                       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

User · Answer

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^[^.^{^}^ ]' );

User · Answer

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

User · Answer

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

User · Answer

Compare the length using lengthB and length function in oracle   SELECT   FROM test WHERE length sampletext   lt   lengthb sampletext

[sql] Oracle query to identify columns having special characters

Examples related to sql

Examples related to oracle