Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Min and Max Number from string Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 30, 2014 8:16 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, October 12, 2016 1:16 PM Points: 128, Visits: 394
 Hello everyoneI 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 numbersee 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 max1,11-12ABCDEF(Y) 1 12 1,11ABCDEF(Y) 1 11 1,3-4AB1-3CD1-2,4EF(Y) 1 41,3-4MTWRF(Y) 1 41,3ABCDEF(Y) 1 31,3MTWRF(Y) 1 3 `please help me to develop this logic.Thanks
Post #1566419
 Posted Wednesday, April 30, 2014 8:56 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 5:28 PM Points: 8,211, Visits: 17,766
 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) maxvalueFROM #temp tCROSS APPLY dbo.PatternSplitCM(Code, '%[0-9]%')WHERE Matched = 1GROUP BY Code` 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
Post #1566438
 Posted Wednesday, April 30, 2014 9:17 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:35 PM Points: 42,070, Visits: 39,452
 yogi123 (4/30/2014)Hello everyoneI 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 numbersee 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 max1,11-12ABCDEF(Y) 1 12 1,11ABCDEF(Y) 1 11 1,3-4AB1-3CD1-2,4EF(Y) 1 41,3-4MTWRF(Y) 1 41,3ABCDEF(Y) 1 31,3MTWRF(Y) 1 3 `please help me to develop this logic.ThanksWould 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1566455
 Posted Wednesday, April 30, 2014 10:02 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, October 12, 2016 1:16 PM Points: 128, Visits: 394
 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 14is anyone have solution for this?
Post #1566480

 Permissions