OraFAQ Forum: SQL & PL/SQL » To differentiate between numeric and char values in a char column

Home » SQL & PL/SQL » SQL & PL/SQL » To differentiate between numeric and char values in a char column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To differentiate between numeric and char values in a char column [message #9908]

Fri, 12 December 2003 12:25

Jay
Messages: 127
Registered: October 1999

Senior Member

Hi,

How can I differentiate between numeric and char value sin a varchar column?
LIke suppose I have a colum VAL in the database which can have say

000123000
0testing123

Now I want to build a query which can return me the rows which have pure numeric values, in the above case it should return me 000123000

also I want to build a reverse query which can return me rows having char values like in the above case it should return me
0testing123

Pls help.

Thanks
Jay

Report message to a moderator

Re: To differentiate between numeric and char values in a char column [message #9910 is a reply to message #9908]

Fri, 12 December 2003 13:17

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

The thread right below yours is similar, so this is a variation on that theme:

select c
  from t
 where replace(translate(c, '0123456789', '0'), '0') is null;

If we convert all numbers to 0 (zero), and then pull out all zeros, is there anything left? If so, it ain't a pure number.

IS NULL will give you the pure numbers (assuming we don't have to worry about decimal points, commas, etc.) and IS NOT NULL will show you the "non" numbers.

Report message to a moderator

Re: To differentiate between numeric and char values in a char column [message #9912 is a reply to message #9908]

Fri, 12 December 2003 14:02

Jay
Messages: 127
Registered: October 1999

Senior Member

Thanks Todd.....I came across another solution.....

SELECT * FROM TABLE WHERE ASCII(COLUMN) BETWEEN 48 AND 57

Report message to a moderator

Re: To differentiate between numeric and char values in a char column [message #9913 is a reply to message #9912]

Fri, 12 December 2003 14:55

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Um, that query does not answer your question. You did test your "solution", right?

The ASCII function only evaluates the first character of a string. Personally, I do not consider '123XXX456' to be a number.

Report message to a moderator