Returning MAX(Value) in String

  • Hello comunity

    I need help to create a TSQL to return MAX(Value) removing the first part and last part.

    Example

    i have these Varchar Reference code:

    1.00001-Q1

    2.00100-Q2

    3.00005-Q4

    I need to cut the string to find the max number excluding (Part1):

    1.

    2.

    3.

    and also excluding (Part3:

    -Q1

    -Q2

    -Q4

    in this case converting varchar to INT, the correct value that i want is:

    00101

    The middle part excluding (Part1 and Part3)

    Then my final reference could be:

    1.00101-Q1

    or

    2.00101-Q2

    or

    3.00101-Q4

    Many thanks,

    Luis SAntos

  • Wow talk about sparse on details. It is hard to figure out what you are trying to do here. You really need to read up on normalization. You should NOT be storing multiple "parts" or values in a single column. This makes things far more difficult to work with.

    Here is my guess based on your post:

    declare @something table

    (

    SomeValue varchar(25)

    )

    insert @something

    select '1.00001-Q1' union all

    select '2.00100-Q2' union all

    select '3.00005-Q4'

    select max(cast(PARSENAME(REPLACE(SomeValue, '-', '.'), 2) as int))

    from @something

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is a possible solution. Be sure to understand how does it work and ask any questions that you have.

    SELECT Reference,

    PARSENAME( r.dotted, 3) + '.' +

    MAX( PARSENAME( r.dotted, 2)) OVER (PARTITION BY (SELECT NULL)) + '-' +

    PARSENAME( r.dotted, 1)

    FROM #SampleData s

    CROSS APPLY (SELECT REPLACE( Reference, '-', '.') dotted) r

    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
  • Hello Sean and Luis

    Sean and Luis , i try your scripts and they return what i need.

    Many thanks,

    Best regards,

    Luis SAntos

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

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