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:

-- 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
;

Start the conversation

0 comments

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.