Manage Learn to apply best practices and optimize your operations.

How to find VARCHAR2 columns that contain only numbers

Check if a VARCHAR2 column contains a valid number using a pure SQL statement.

In PL/SQL, you can use the "to_number" function to check if a VARCHAR2 column contains a valid number. However, the code below shows how to do this with a pure SQL statement.

The first few statements are meant to create the table and rows for conducting the test. The key to the solution is in the WHERE clause. Use SQL*PLUS to conduct the test.

Create table accnt
(
Name varchar2(50),
AccountId varchar2(50)
);

Insert into accnt values
('Peter', '1234');

Insert into accnt values
('Pan', '1234a');

Insert into accnt values
('Joe', '1234D');

commit;

-- The following SQL gives only AccountIds that ARE numbers

select rpad(name, 40), rpad(accountid,20)
from accnt
where
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(upper(AccountId), '1', 'a'), '2', 'a'), '3','a'),'4','a'),'5','a'),'6','a'),'7','a'),'8','a'),'9','a'),'0','a')  
like trim(rpad(' ', length(AccountId) + 1, 'a'))
;

-- The following SQL gives only AccountIds that ARE NOT numbers
select rpad(name, 40), rpad(accountid,20)
from accnt
where
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(upper(AccountId), '1', 'a'), '2', 'a'), '3','a'),'4','a'),'5','a'),'6','a'),'7','a'),'8','a'),'9','a'),'0','a')  
NOT like trim(rpad(' ', length(AccountId) + 1, 'a'))
;

Reader Feedback

Rich S. writes: I think this is easier to follow:

table accnt
Name varchar2(50),
Account_Id varchar2(50)

The table contains:

NAME ACCOUNT_ID
Peter 1234
Pan 1234a
Joe 1234D

-- The following SQL gives only AccountIds that ARE numbers
SELECT NAME, ACCOUNT_ID
FROM ACCNT 
WHERE ACCOUNT_ID IS NOT NULL
AND LENGTH(TRANSLATE('*'||ACCOUNT_ID,'*0123456789','*'))=1
;

-- The following SQL gives only AccountIds that ARE NOT numbers
SELECT NAME, ACCOUNT_ID
FROM ACCNT 
WHERE ACCOUNT_ID IS NOT NULL
AND LENGTH(TRANSLATE('*'||ACCOUNT_ID,'*0123456789','*'))>1
;

Frans E. writes: I like this query better than the above tip, because it's shorter. To check whether there is alphanumerical information in a varchar2 column:

LENGTH(LTRIM(RTRIM((TRANSLATE (column_name,'0123456789', '')))))>0

You can detect only numbers by making it:

LENGTH(LTRIM(RTRIM((TRANSLATE (column_name,'.,0123456789', '')))))=0

You can make it only alphanumerical by making it:

LENGTH(LTRIM(RTRIM((TRANSLATE (column_name,'0123456789', '')))))=LENGTH(LTRIM(RTRIM((TRANSLATE (column_name)))

Thanks to Roel van Ree who first suggested this query.

Maciej Z.. writes: I found a simpler solution to this problem:

-- The following SQL gives only AccountIds that ARE numbers
select rpad(name, 40), rpad(accountid,20)
from accnt
where rtrim(accountid,'0123456789') is null

-- The following SQL gives only AccountIds that ARE NOT numbers
select rpad(name, 40), rpad(accountid,20)
from accnt
where
where rtrim(accountid,'0123456789') is not null

Raj T. writes: I think this solution is 1) Very error-prone -- I may need to count my "replaces" each time to make sure I did not put too little or too many of them; and 2) Too complicated to someone other than a programmer to understand and maintain unless well documented. Instead, we could use something as simple as:

-- The following SQL gives only AccountIds that ARE numbers
select name, AccountId  from accnt
   where ltrim(translate(AccountId , '0123456789','          ')) is null;

-- The following SQL gives only AccountIds that ARE NOT numbers
select name, AccountId  from accnt
   where ltrim(translate(AccountId , '0123456789','          ')) is not null;

Frank P. writes: This is a good idea that works. However, there is a smarter way to find the number and number-only accounts:

-- The following SQL gives only AccountIds that ARE numbers 
select rpad(name, 40), rpad(accountid,20) 
from accnt 
where replace(translate(AccountId,'0123456789','0000000000'),'0') is null 
; 

-- The following SQL gives only AccountIds that ARE NOT numbers 
select rpad(name, 40), rpad(accountid,20) 
from accnt 
where replace(translate(AccountId,'0123456789','0000000000'),'0') is not null
;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close