Results 1 to 2 of 2

Thread: Retrieve maximum number from alphanumeric column in Sql

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2014
    Posts
    16

    Retrieve maximum number from alphanumeric column in Sql

    Please can someone help me with SQL query how to get maximum number from an alphanumeric field. Below is the column values. As you can see I want to retrieve the highest number as OSS120 in the below values. The first 3 letters are always constant. Thanks in advance.

    The below query retrieved OSS7 instead of OSS120 as the highest number in the list.
    Code:
    select MailRefNo from MailRegistry
          where REPLACE(MailRefNo,'[A-Za-z]','')= 
              (select max(REPLACE(MailRefNo,'[A_Za-z]',''))  
                   from MailRegistry)
    ASG1
    ASG10
    ASG14
    ASG2
    ASG21
    ASG4
    LTS1
    LTS10
    LTS14
    LTS2
    LTS21
    LTS4
    OSS1
    OSS10
    OSS100
    OSS11
    OSS114
    OSS120
    OSS2
    OSS21
    OSS3
    OSS4
    OSS5
    OSS6
    OSS7

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Retrieve maximum number from alphanumeric column in Sql

    ^That's because it's text ... so it is doing a text-based sort ... what you need to do is extract the numbers, convert them to a numerical datatype, then take the max there.

    (select max(convert(int, REPLACE(MailRefNo,'[A_Za-z]','')))

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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