Question

Checking Whether Field Is Digit only or Having special Characters

  • 15 December 2023
  • 1 reply
  • 53 views
haritha.waidyaratne
Rank
Badge +4

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.

1 reply

JJäthing
Rank
Userlevel 4
Badge +9

Hi 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


Terms & ConditionsCookie settings