Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Returning MAX(Value) in String Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:34 AM
Points: 275, Visits: 590
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



Post #1597304
Posted Tuesday, July 29, 2014 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597310
Posted Tuesday, July 29, 2014 8:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,066, Visits: 9,235
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1597319
Posted Tuesday, July 29, 2014 8:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:34 AM
Points: 275, Visits: 590
Hello Sean and Luis

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

Many thanks,
Best regards,
Luis SAntos



Post #1597341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse