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

Min and Max Number from string Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
code				min	max
1,11-12ABCDEF(Y) 1 12
1,11ABCDEF(Y) 1 11
1,3-4AB1-3CD1-2,4EF(Y) 1 4
1,3-4MTWRF(Y) 1 4
1,3ABCDEF(Y) 1 3
1,3MTWRF(Y) 1 3


please help me to develop this logic.
Thanks
Post #1566419
Posted Wednesday, April 30, 2014 8:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
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 #1566438
Posted Wednesday, April 30, 2014 9:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
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
code				min	max
1,11-12ABCDEF(Y) 1 12
1,11ABCDEF(Y) 1 11
1,3-4AB1-3CD1-2,4EF(Y) 1 4
1,3-4MTWRF(Y) 1 4
1,3ABCDEF(Y) 1 3
1,3MTWRF(Y) 1 3


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1566455
Posted Wednesday, April 30, 2014 10:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342


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?
Post #1566480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse