Min and Max Number from string

  • Hello everyone

    I need help to develop logic. In my query i have Alpha Numeric string with commas and hyphens.so from the string i need to populate min and max number

    see below for DDL

    create table #temp

    (Code varchar(50))

    insert into #temp values ('1,11-12ABCDEF(Y)')

    insert into #temp values ('1,11ABCDEF(Y)')

    insert into #temp values ('1,3-4AB1-3CD1-2,4EF(Y)')

    insert into #temp values ('1,3-4MTWRF(Y)')

    insert into #temp values ('1,3ABCDEF(Y)')

    insert into #temp values ('1,3MTWRF(Y)')

    below is the desired output

    codeminmax

    1,11-12ABCDEF(Y)112

    1,11ABCDEF(Y)111

    1,3-4AB1-3CD1-2,4EF(Y)14

    1,3-4MTWRF(Y)14

    1,3ABCDEF(Y)13

    1,3MTWRF(Y)13

    please help me to develop this logic.

    Thanks

  • The Pattern Splitter made by Chris Morris is able to help you in this situation. You can read about it in this article: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

    And you'll end up with something like this:

    SELECT Code, MIN( Item) minvalue, MAX( Item) maxvalue

    FROM #temp t

    CROSS APPLY dbo.PatternSplitCM(Code, '%[0-9]%')

    WHERE Matched = 1

    GROUP BY Code

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • yogi123 (4/30/2014)


    Hello everyone

    I need help to develop logic. In my query i have Alpha Numeric string with commas and hyphens.so from the string i need to populate min and max number

    see below for DDL

    create table #temp

    (Code varchar(50))

    insert into #temp values ('1,11-12ABCDEF(Y)')

    insert into #temp values ('1,11ABCDEF(Y)')

    insert into #temp values ('1,3-4AB1-3CD1-2,4EF(Y)')

    insert into #temp values ('1,3-4MTWRF(Y)')

    insert into #temp values ('1,3ABCDEF(Y)')

    insert into #temp values ('1,3MTWRF(Y)')

    below is the desired output

    codeminmax

    1,11-12ABCDEF(Y)112

    1,11ABCDEF(Y)111

    1,3-4AB1-3CD1-2,4EF(Y)14

    1,3-4MTWRF(Y)14

    1,3ABCDEF(Y)13

    1,3MTWRF(Y)13

    please help me to develop this logic.

    Thanks

    Would the smallest number be "1" and the largest number be "14" for the following?

    7,3-4AB1-3CD1-2,14EF(Y)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Would the smallest number be "1" and the largest number be "14" for the following?

    7,3-4AB1-3CD1-2,14EF(Y)

    Yes you correct the max number is 14

    is anyone have solution for this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply