Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ireland
    Posts
    122
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Oracle: extract part of field and create next id

    Hi guys,

    I need to extract all fields that begin with "AW", there are numbers after this i.e AW11, AW12.....

    Next i need to find the largert number i.e 12 in this case. So far i have

    Code:
    my $product_query = qq(SELECT code,
                             FROM product
                             WHERE code LIKE 'AW%');
    Thanks in advance

  • #2
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    This will chop off the first two characters and select the highest number.

    select max(to_number(substr(CODE, 3, length(code)-2))) from product

    However, I would recommend storing just the number and prepending "AW" to it when you need to print the number out.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •