Returning MAX(Value) in String

Question

Returning MAX(Value) in String

luissantos

SSCertifiable

Points: 7391
More actions
July 29, 2014 at 7:57 am

#288586

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

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello Sean and Luis

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

Many thanks,

Best regards,

Luis SAntos