
Determining if a data is an integer or not?
Is there a way in Oracle SQL that I can look at a position of a column and determine if that data is an integer or not?
The column is defined as a varchar2 and I know I need to subscript into it(second position for 1 byte) but I don't know how to test for 09 without coding 0,1,2,3,4,5,6,7,8,9.
The data would look something like "CJE20020" and I want to include data with noninterger values(i.e. include the amount for this activity since the second position is a "J").

I am not clear what you want ..
Extract second char and
and use any number function to find it is number or not
If not number it will error out
example
select abs(itmid) from ccs_orders where rownum < 2
*
ERROR at line 1:
ORA01722: invalid number
If it is number
SQL> select abs(acweek) from ccs_orders where rownum < 2;
ABS(ACWEEK)

1
Raghu

do you mean something like ...
where translate(substr(my_column,2,1),'1234567890','0000000000')) = '0'

Originally posted by slimdave
do you mean something like ...
where translate(substr(my_column,2,1),'1234567890','0000000000')) = '0'
This might be what I am looking for. I'll give it a shot and see what get. Thank you.

Why are you limited to using only SQL? Test for an integer? You could use the mod function. mod (number/1) not equal to zero > number is not an integer. Create a function, pass in the number.

Well to clarify things a bit, I won't be translating anything. I'm just trying to select data where the second position is alphabetical(i.e. az) so is there a way I can specify that without actually typing out all the letters in the alphabet?
I know I could do it with something like .. SUBTR(columnname,2,1) in 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' but I'm assuming there some decent class function or something I could use?

translate(upper(substr(my_column,2,1)),'QWERTYUIOPASDFGHJKLZXCVBNM','AAAAAAAAAAAAAAAAAAAAAAAAAAA')) = 'A'
would do it (check the number of A's, not sure i got it right), or you could check for an ascii code BETWEEN two appropriate values.

Can you tell me what's the purpose of arranging the values "QWERTYUIOPASDFGHJKLZXCVBNM" in that order? I counted the number of A's and there are 27 of them. Suppose to be 26?

Jeez, ever look at the keyboard? What is the first letter in the top left corner?

That's why I am a junior member....
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
