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

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

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

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