Find out if field value is only letter

[pyro]

Personal, is there a more functional way to find out if the contents of a varchar2 is composed of letters only?
I thought about clearing special characters and numbers with Replace () and then measuring the size of the variable and compare with the original size if it is different because it had "trash".
But do this way it seems very mechanical, has anyone ever developed something more functional?

[victorhugomuniz]

You can give a to_number and wait for an exception

[pyro]

Okay, in this case you solve, but when I need to validate if only numbers came?

[burga]

Hi Pyro,

in 10g / 11g You can find records that only have letters using regExp_Like.

Select all

WITH tabela AS ( 
  SELECT 'aaS123Iiouh' campo FROM DUAL 
  UNION ALL 
  SELECT 'aaSadsUIiouh' FROM DUAL 
  UNION ALL 
  SELECT 'aaSAAAIiouh' FROM DUAL 
  UNION ALL 
  SELECT '00' FROM DUAL 
  UNION ALL 
  SELECT '123a' FROM DUAL 
  UNION ALL 
  SELECT '' FROM DUAL) 
SELECT campo  
FROM tabela  
WHERE REGEXP_LIKE(campo,'^[[:alpha:]]+$');

I will leave home lesson the return of records that only have number ... change is minimal in this code and even half obvious ... : Wink: