locked
extract maximum number embeded in string RRS feed

  • Question

  • User1395831461 posted

    I have a table with a column containing strings each of which has a number embedded in it...

    COL

    boa001

    boa002

    boa003

    xkar001

    xkar002

    ab001

    etc... from which I want to extract the highest numeric value from each occurrence of the string portion.

    So for the 'boa' string I want to return 003, from 'xkar' I want to return 002 and from 'ab' I want to extract 001.

    Could someone kindly show me how I might do this? 

    Thanks tonnes for any help, Roscoe

    Tuesday, November 15, 2016 3:39 PM

Answers

  • User753101303 posted

    But you won't have something such as x3kay2 or xkay002a ? So it would be from the first digit position to the end of string? And you'll also always have at least one digit at the end ?

    You could liekly use then https://technet.microsoft.com/en-us/library/ms188395(v=sql.110).aspx to locate the first 0 to 9 digit. Try first to see if PATINDEX('%[0-9]%',YourColumn) returns the position for the first digit and then start from that to built your final expression.

    The principle will be similar ie you'll have to use string functions found at https://technet.microsoft.com/en-us/library/ms181984(v=sql.110).aspx to separate the two pieces of information you have in this column according to your storage rules.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 15, 2016 4:17 PM
  • User-1716253493 posted

    Try this

    select SUBSTRING(COL, PATINDEX('%[0-9]%', COL), LEN(COL))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 15, 2016 4:27 PM

All replies

  • User753101303 posted

    Hi,

    And this is always the last 3 characters ?

    If seems something such as (untested) :

    SELECT LEFT(YourColumn,LEN(YourColumn)-3),MAX(RIGHT(YourColumn,3))
    FROM YourTable
    GROUP BY LEFT(YourColumn,LEN(YourColumn)-3)

    might do the job that is it is grouping on all characters except the 3 last one and taking the max value for the last 3 characters.

    Also it usually means that you are actually storing two information into a single column. It is likely easier/more efficient to use two separate columns for storage even if you concat/split them when viewed/edited by the user rather than to use a single column and having to split them for processing.

    Tuesday, November 15, 2016 3:57 PM
  • User1395831461 posted

    Hi Patrice, Thanks for the reply.  No does not always have to be the last three characters, could be of form xkay1 or xkay10001.

    Point taken about combining columns...I would not have done it this way it was presented to me

    Tuesday, November 15, 2016 4:06 PM
  • User753101303 posted

    But you won't have something such as x3kay2 or xkay002a ? So it would be from the first digit position to the end of string? And you'll also always have at least one digit at the end ?

    You could liekly use then https://technet.microsoft.com/en-us/library/ms188395(v=sql.110).aspx to locate the first 0 to 9 digit. Try first to see if PATINDEX('%[0-9]%',YourColumn) returns the position for the first digit and then start from that to built your final expression.

    The principle will be similar ie you'll have to use string functions found at https://technet.microsoft.com/en-us/library/ms181984(v=sql.110).aspx to separate the two pieces of information you have in this column according to your storage rules.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 15, 2016 4:17 PM
  • User-1716253493 posted

    Try this

    select SUBSTRING(COL, PATINDEX('%[0-9]%', COL), LEN(COL))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 15, 2016 4:27 PM