Convert a varchar to a number
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Convert a varchar to a number

  1. #1
    Join Date
    Dec 2001
    Posts
    6

    Question

    Hi all,

    I've some problem i'd like with a query to have a numeric part
    of a field that contains alpha-numeric

    e.a
    ----

    Streetnumber : 25/b ---> 25
    115b5 --> 115
    140/ --> 140



    Thx in advance


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    What is the logic of the alpha-numeric fields. Is it so that first you have the numbers and then the letters?


  3. #3
    Join Date
    Dec 2001
    Posts
    6
    yes , i know that but i'd like to know if a function exist to retrieve only the numeric part of the data ?


    Thx in advance

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Here you are:

    1 select translate('Streetnumber : 25/b',
    2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#%&/()=+?,.;:* -',
    3* '01234567890') from dual

    TR
    --
    25


    1 select translate('115b5',
    2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#%&/()=+?,.;:* -',
    3* '01234567890') from dual

    TRAN
    ----
    1155


    1 select translate('140',
    2 '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#%&/()=+?,.;:* -',
    3* '01234567890') from dual

    TRA
    ---
    140

    If you need to eliminat emore symbols, you can add symbols to the long string.

  5. #5
    Join Date
    Dec 2001
    Posts
    6
    Your query is good only for number who has only alpha in the end e.a:

    25/b --> 25 good
    115b5 --> 1155 not good but 115

    in fact i just want the number before the 1st character non numeric

    thx for you reply

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    try this:

    select substr(translate('140-34',
    '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#%&/()=+?,.;:* -',
    '01234567890qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'),1,
    instr(translate('140-',
    '01234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm!"#%&/()=+?,.;:* -',
    '01234567890qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'),'q')-1) from dual
    /
    Cheers!
    OraKid.

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