Hello,
I found these pretty useful -
REGEXP_LIKE([num field], '^[[:digit:]]+$') -- Checks whether the concerned value/column contains digits only
REGEXP_INSTR(SUBSTR([no special characters], '[^a-z^A-Z^0-9]') = 0 -- Checks whether the concerned value/column contains NO special characters (note the return value; greater than zero if special char found.
»
sql - How can you tell if a value is not numeric in Oracle? - Stack Overflow
OraFAQ Forum: SQL & PL/SQL » How do I find special characters using a regular expresion?
Thank you and enjoy.
Question
Checking Whether Field Is Digit only or Having special Characters
+5
+11Hi Haritha,
I once learned a way to find special characters in an IFS APPS window using the SQL code shown below. The case description is:
Someone created an object item with the ID ‘M60%E’. In this case the use of the %-character makes the specific item impossible to query for since that is a wildcard character.
The method to find items with such a character is by using an ‘ESCAPE’ command in the where clause:
OBJECT_ID LIKE '%@%%' ESCAPE'@'
...where the @ character can be just any key. The ‘%’ directly after the ‘@’ is the character in the table you want to identify.
It doesn’t seem the system allows you to enter more than one ESCAPE at a time.
OBJECT_ID LIKE '%@%%' ESCAPE'@'
OBJECT_ID LIKE '%@_%' ESCAPE'@'
And it does not allow for finding semicolons.
Sincerely
Jonas
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.