Chris and Joel,
In Oracle Database 10G, you can display alphabets and numbers using the regexp_like expression.

--Select all rows that have only alphabets in the text1 column
--It does not select rows that has a comma or a digit in it
select text1
from test2
where regexp_like(text1, '^[^\,^\0-9]{1,30}$')
/

--Select all rows that have only numbers in the text1 column
--It does not select rows that has a comma or an alphabet in it
select text1
from test2
where regexp_like(text1, '^[^\,^\a-z A-Z]{1,30}$')
/

--Select all rows that have alphanumeric (alphabets and numbers) in the text1 column
select text1
from test2
where regexp_like(text1,'[[:alnum:]]')
/


In SQL Server, you use the CASE statement to do this. So I sent the following SQL Server code to the "Oracle #1 developer" (Oh my! Phew!) to see if it could be done!

SELECT Text1,
CASE WHEN RTRIM(Text1) NOT LIKE '%[^0-9]%' THEN 'Yes' ELSE 'No' END 'All Numbers',
CASE WHEN RTRIM(Text1) NOT LIKE '%[^a-Z]%' THEN 'Yes' ELSE 'No' END 'All Alphabets'
FROM Test2


I cannot release the name of the "Oracle #1 developer" (Oh my! Phew!) because its "Top Secret" and therefore if I told you, I would have to shoot you! hehe!



Anyway, here is the code from the "Oracle #1 developer"! hehe!

select text1,
case when replace(translate(text1,'0123456789','0000000000'),'0') is null
then 'all numbers'
when replace(translate(lower(text1),'abcdefghijklmnopqrstuvwxyz',
rpad('#',26,'#')),'#') is null
then 'all chars' else 'mixed' end status
from test2
/









Oracle LazyDBA home page