Min and Max Number from string

• yogi123

  SSCrazy

  Points: 2932

  More actions

  April 30, 2014 at 8:16 am

  #306945

  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

  codeminmax

  1,11-12ABCDEF(Y)112

  1,11ABCDEF(Y)111

  1,3-4AB1-3CD1-2,4EF(Y)14

  1,3-4MTWRF(Y)14

  1,3ABCDEF(Y)13

  1,3MTWRF(Y)13

  please help me to develop this logic.

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 30, 2014 at 8:56 am

  #1709871

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

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  April 30, 2014 at 9:17 am

  #1709886

  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

  codeminmax

  1,11-12ABCDEF(Y)112

  1,11ABCDEF(Y)111

  1,3-4AB1-3CD1-2,4EF(Y)14

  1,3-4MTWRF(Y)14

  1,3ABCDEF(Y)13

  1,3MTWRF(Y)13

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• yogi123

  SSCrazy

  Points: 2932

  More actions

  April 30, 2014 at 10:02 am

  #1709907

  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?