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: Today @ 6:25 PM
Points: 3,333, Visits: 7,193
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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