DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Determining if a data is an integer or not?

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    29

    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 0-9 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 non-interger values(i.e. include the amount for this activity since the second position is a "J").

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    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:
    ORA-01722: invalid number

    If it is number

    SQL> select abs(acweek) from ccs_orders where rownum < 2;

    ABS(ACWEEK)
    -----------
    1
    Raghu

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    do you mean something like ...

    where translate(substr(my_column,2,1),'1234567890','0000000000')) = '0'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2002
    Posts
    29
    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.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    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.

  6. #6
    Join Date
    May 2002
    Posts
    29
    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. a-z) 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(column-name,2,1) in 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' but I'm assuming there some decent class function or something I could use?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2002
    Posts
    29
    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?

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    Jeez, ever look at the keyboard? What is the first letter in the top left corner?

  10. #10
    Join Date
    May 2002
    Posts
    29
    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
  •  


Click Here to Expand Forum to Full Width