String Parsing Hell

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 18, 2009 at 2:14 pm

  #143001

  Ok I have the following data (exhumed out of a production system).

  CREATE TABLE #TEst

  (

  TESTID INT PRIMARY KEY,

  VERSIONVAL CHAR(10) NOT NULL

  )

  INSERT INTO #TEST(TESTID,VERSIONVAL)

  SELECT 1,'1.0.0'

  UNION ALL

  SELECT 575,'7.4.0'

  UNION ALL

  SELECT 576,'4.4.0'

  UNION ALL

  SELECT 577,'3.4.0'

  UNION ALL

  SELECT 578,'7.4.0'

  UNION ALL

  SELECT 6,'3.0.0'

  UNION ALL

  SELECT 7,'1.2.2'

  UNION ALL

  SELECT 579,'6.5.3'

  UNION ALL

  SELECT 580,'3.5.0'

  UNION ALL

  SELECT 581,'4.5.0'

  UNION ALL

  SELECT 11,'2.0.0'

  UNION ALL

  SELECT 12,'2.0D'

  UNION ALL

  SELECT 13,'2.0F'

  UNION ALL

  SELECT 14,'2.0H'

  UNION ALL

  SELECT 582,'7.5.0'

  UNION ALL

  SELECT 583,'6.5.3'

  UNION ALL

  SELECT 17,'2.2.0'

  UNION ALL

  SELECT 584,'6.5.4'

  UNION ALL

  SELECT 585,'7.4'

  UNION ALL

  SELECT 586,'7.5'

  UNION ALL

  SELECT 21,'2.0E'

  UNION ALL

  SELECT 589,'7.5.0'

  UNION ALL

  SELECT 590,'3.5.0'

  UNION ALL

  SELECT 591,'6.6.0'

  UNION ALL

  SELECT 592,'7.5.1'

  UNION ALL

  SELECT 593,'7.5.1'

  UNION ALL

  SELECT 594,'7.5.0'

  UNION ALL

  SELECT 595,'7.5.0'

  UNION ALL

  SELECT 596,'7.5.0'

  UNION ALL

  SELECT 597,'7.5.0'

  UNION ALL

  SELECT 598,'7.5.0'

  UNION ALL

  SELECT 599,'3.6.0'

  UNION ALL

  SELECT 601,'7.6'

  UNION ALL

  SELECT 34,'4.0G'

  UNION ALL

  SELECT 602,'7.6.0'

  UNION ALL

  SELECT 603,'7.6.0'

  UNION ALL

  SELECT 37,'5.0'

  UNION ALL

  SELECT 38,'5.01'

  UNION ALL

  SELECT 39,'5.02'

  UNION ALL

  SELECT 40,'5.03'

  UNION ALL

  SELECT 41,'5.0A'

  UNION ALL

  SELECT 42,'5.0B'

  UNION ALL

  SELECT 43,'5.0C'

  UNION ALL

  SELECT 44,'5.0D'

  UNION ALL

  SELECT 45,'5.0E'

  UNION ALL

  SELECT 46,'6.0'

  UNION ALL

  SELECT 47,'6.01'

  UNION ALL

  SELECT 48,'6.02'

  UNION ALL

  SELECT 49,'6.03'

  UNION ALL

  SELECT 604,'7.6.0'

  UNION ALL

  SELECT 51,'6.10'

  UNION ALL

  SELECT 52,'6.10'

  UNION ALL

  SELECT 608,'4.0.0'

  UNION ALL

  SELECT 609,'4.0.0'

  UNION ALL

  SELECT 55,'6.20'

  UNION ALL

  SELECT 610,'8.0.0'

  UNION ALL

  SELECT 611,'7.0.0'

  UNION ALL

  SELECT 98,'10'

  UNION ALL

  SELECT 59,'1.3.0'

  UNION ALL

  SELECT 60,'1.3.1'

  UNION ALL

  SELECT 61,'1.3.2'

  UNION ALL

  SELECT 612,'3.6.0'

  UNION ALL

  SELECT 614,'4.6'

  UNION ALL

  SELECT 71,'1.3.3'

  UNION ALL

  SELECT 73,'6.30'

  UNION ALL

  SELECT 74,'7.0'

  UNION ALL

  SELECT 75,'2.2.0'

  UNION ALL

  SELECT 79,'1.3.4'

  UNION ALL

  SELECT 81,'2.0.1'

  UNION ALL

  SELECT 91,'7.0'

  UNION ALL

  SELECT 118,'6.20'

  UNION ALL

  SELECT 168,'3.0'

  UNION ALL

  SELECT 296,'1'

  UNION ALL

  SELECT 310,'@2.85'

  UNION ALL

  SELECT 314,'2.3.2'

  UNION ALL

  SELECT 318,'15'

  UNION ALL

  SELECT 319,'2.1.3'

  UNION ALL

  SELECT 320,'2.3.0'

  UNION ALL

  SELECT 321,'2.1.3'

  UNION ALL

  SELECT 76,'2.0.2'

  UNION ALL

  SELECT 77,'2.1.0'

  UNION ALL

  SELECT 78,'2.3.0'

  UNION ALL

  SELECT 80,'1.1.0'

  UNION ALL

  SELECT 82,'2.2.2'

  UNION ALL

  SELECT 93,'6.30'

  UNION ALL

  SELECT 95,'6.03'

  UNION ALL

  SELECT 97,'6.10'

  UNION ALL

  SELECT 83,'2.2.3'

  UNION ALL

  SELECT 84,'2.2.4'

  UNION ALL

  SELECT 85,'2.2.5'

  UNION ALL

  SELECT 86,'2.2.6'

  UNION ALL

  SELECT 87,'2.2.7'

  UNION ALL

  SELECT 88,'2.2.8'

  UNION ALL

  SELECT 89,'2.2.9'

  UNION ALL

  SELECT 90,'2.3.1'

  UNION ALL

  SELECT 92,'6.30'

  UNION ALL

  SELECT 94,'6.30'

  UNION ALL

  SELECT 96,'6.20'

  UNION ALL

  SELECT 99,'11'

  UNION ALL

  SELECT 120,'6.032'

  UNION ALL

  SELECT 122,'3.12-25'

  UNION ALL

  SELECT 100,'12'

  UNION ALL

  SELECT 101,'13'

  UNION ALL

  SELECT 102,'14'

  UNION ALL

  SELECT 103,'15'

  UNION ALL

  SELECT 104,'16'

  UNION ALL

  SELECT 105,'17'

  UNION ALL

  SELECT 106,'18'

  UNION ALL

  SELECT 107,'19'

  UNION ALL

  SELECT 108,'20'

  UNION ALL

  SELECT 109,'21'

  UNION ALL

  SELECT 110,'22'

  UNION ALL

  SELECT 111,'23'

  UNION ALL

  SELECT 112,'24'

  UNION ALL

  SELECT 113,'25'

  UNION ALL

  SELECT 114,'26'

  UNION ALL

  SELECT 115,'27'

  UNION ALL

  SELECT 116,'28'

  UNION ALL

  SELECT 119,'6.101'

  UNION ALL

  SELECT 121,'6.03'

  UNION ALL

  SELECT 123,'4.3.02'

  UNION ALL

  SELECT 124,'4.5.02'

  UNION ALL

  SELECT 125,'4.5.03'

  UNION ALL

  SELECT 126,'4.5.04'

  UNION ALL

  SELECT 127,'4.5.05'

  UNION ALL

  SELECT 128,'4.6.03C'

  UNION ALL

  SELECT 129,'4.7.00'

  UNION ALL

  SELECT 130,'4.7.01'

  UNION ALL

  SELECT 131,'4.8.00'

  UNION ALL

  SELECT 132,'6.03'

  UNION ALL

  SELECT 133,'6.1B'

  UNION ALL

  SELECT 134,'6.2'

  UNION ALL

  SELECT 135,'5.0D'

  UNION ALL

  SELECT 136,'5.0E'

  UNION ALL

  SELECT 137,'6.03'

  UNION ALL

  SELECT 138,'6.031'

  UNION ALL

  SELECT 139,'6.0312'

  UNION ALL

  SELECT 140,'6.032'

  UNION ALL

  SELECT 141,'6.10'

  UNION ALL

  SELECT 162,'6.032'

  UNION ALL

  SELECT 143,'5.02'

  UNION ALL

  SELECT 144,'5.0A'

  UNION ALL

  SELECT 145,'5.0B'

  UNION ALL

  SELECT 146,'5.0C'

  UNION ALL

  SELECT 147,'5.0E'

  UNION ALL

  SELECT 148,'6.03'

  UNION ALL

  SELECT 149,'6.031'

  UNION ALL

  SELECT 150,'3.30'

  UNION ALL

  SELECT 151,'4.00'

  UNION ALL

  SELECT 152,'6.032'

  UNION ALL

  SELECT 153,'6.20'

  UNION ALL

  SELECT 154,'6.30'

  UNION ALL

  SELECT 155,'7.00'

  UNION ALL

  SELECT 156,'6.032'

  UNION ALL

  SELECT 157,'6.20'

  UNION ALL

  SELECT 158,'6.30'

  UNION ALL

  SELECT 159,'7.00'

  UNION ALL

  SELECT 160,'6.031'

  UNION ALL

  SELECT 161,'2.2.0'

  UNION ALL

  SELECT 163,'6.302'

  UNION ALL

  SELECT 164,'6.101'

  UNION ALL

  SELECT 165,'5.0B1'

  UNION ALL

  SELECT 166,'6.031'

  UNION ALL

  SELECT 167,'6.21'

  UNION ALL

  SELECT 169,'3.0.1.10'

  UNION ALL

  SELECT 170,'3.5.0.32'

  UNION ALL

  SELECT 171,'3.5.0.33'

  UNION ALL

  SELECT 172,'3.5.0.61'

  UNION ALL

  SELECT 173,'3.5.0.68'

  UNION ALL

  SELECT 174,'3.5.0.71'

  UNION ALL

  SELECT 175,'4.0'

  UNION ALL

  SELECT 176,'4.120'

  UNION ALL

  SELECT 177,'4.122'

  UNION ALL

  SELECT 178,'4.124'

  UNION ALL

  SELECT 179,'4.125'

  UNION ALL

  SELECT 180,'1.21'

  UNION ALL

  SELECT 181,'1.31'

  UNION ALL

  SELECT 182,'2.0'

  UNION ALL

  SELECT 183,'5.03'

  UNION ALL

  SELECT 184,'5.0B'

  UNION ALL

  SELECT 185,'5.0D'

  UNION ALL

  SELECT 186,'5.0C'

  UNION ALL

  SELECT 187,'5.0D1'

  UNION ALL

  SELECT 188,'6.02'

  UNION ALL

  SELECT 189,'6.03'

  UNION ALL

  SELECT 190,'6.20'

  UNION ALL

  SELECT 191,'6.101'

  UNION ALL

  SELECT 192,'6.10'

  UNION ALL

  SELECT 193,'5.0E'

  UNION ALL

  SELECT 194,'5.0A'

  UNION ALL

  SELECT 195,'6.30'

  UNION ALL

  SELECT 196,'1.31'

  UNION ALL

  SELECT 197,'1.4'

  UNION ALL

  SELECT 198,'1.42'

  UNION ALL

  SELECT 199,'1.43'

  UNION ALL

  SELECT 200,'2.0A'

  UNION ALL

  SELECT 201,'2.0F'

  UNION ALL

  SELECT 202,'2.0H'

  UNION ALL

  SELECT 203,'2.5F'

  UNION ALL

  SELECT 204,'4.0E'

  UNION ALL

  SELECT 205,'5.0A'

  UNION ALL

  SELECT 206,'5.0B'

  UNION ALL

  SELECT 207,'5.0C'

  UNION ALL

  SELECT 208,'5.0D'

  UNION ALL

  SELECT 209,'5.0D1'

  UNION ALL

  SELECT 210,'5.0E'

  UNION ALL

  SELECT 211,'6.0'

  UNION ALL

  SELECT 212,'6.02'

  UNION ALL

  SELECT 213,'6.03'

  UNION ALL

  SELECT 214,'6.10'

  UNION ALL

  SELECT 215,'6.20'

  UNION ALL

  SELECT 216,'6.30'

  UNION ALL

  SELECT 217,'7.0'

  UNION ALL

  SELECT 218,'5.0'

  UNION ALL

  SELECT 219,'5.0B'

  UNION ALL

  SELECT 220,'5.0D'

  UNION ALL

  SELECT 221,'5.0E'

  UNION ALL

  SELECT 222,'6.02'

  UNION ALL

  SELECT 223,'6.03'

  UNION ALL

  SELECT 224,'6.10'

  UNION ALL

  SELECT 225,'6.20'

  UNION ALL

  SELECT 226,'6.30'

  UNION ALL

  SELECT 227,'7.0'

  UNION ALL

  SELECT 228,'1.0.0'

  UNION ALL

  SELECT 229,'2.0.0'

  UNION ALL

  SELECT 230,'2.1.0'

  UNION ALL

  SELECT 231,'2.1.1'

  UNION ALL

  SELECT 232,'2.1.2'

  UNION ALL

  SELECT 233,'1.0.0'

  UNION ALL

  SELECT 234,'2.1.0'

  UNION ALL

  SELECT 238,'1.22'

  UNION ALL

  SELECT 239,'1.33'

  UNION ALL

  SELECT 240,'2.0'

  UNION ALL

  SELECT 241,'2.01'

  UNION ALL

  SELECT 242,'2.02'

  UNION ALL

  SELECT 243,'2.10'

  UNION ALL

  SELECT 244,'2.20'

  UNION ALL

  SELECT 245,'6.10'

  UNION ALL

  SELECT 246,'6.20'

  UNION ALL

  SELECT 247,'6.30'

  UNION ALL

  SELECT 248,'1.0G'

  UNION ALL

  SELECT 249,'3.0D'

  UNION ALL

  SELECT 250,'3.0I'

  UNION ALL

  SELECT 251,'4.0'

  UNION ALL

  SELECT 252,'4.0I'

  UNION ALL

  SELECT 253,'4.1'

  UNION ALL

  SELECT 254,'5.0'

  UNION ALL

  SELECT 255,'5.0A'

  UNION ALL

  SELECT 256,'5.0B'

  UNION ALL

  SELECT 257,'5.0C'

  UNION ALL

  SELECT 258,'5.0D'

  UNION ALL

  SELECT 259,'5.0E'

  UNION ALL

  SELECT 260,'5.01'

  UNION ALL

  SELECT 261,'5.02'

  UNION ALL

  SELECT 262,'5.03'

  UNION ALL

  SELECT 263,'6.0'

  UNION ALL

  SELECT 264,'6.02'

  UNION ALL

  SELECT 265,'6.03'

  UNION ALL

  SELECT 266,'7.0'

  UNION ALL

  SELECT 267,'3.0I'

  UNION ALL

  SELECT 268,'1.31'

  UNION ALL

  SELECT 269,'1.32'

  UNION ALL

  SELECT 270,'1.4'

  UNION ALL

  SELECT 271,'1.41'

  UNION ALL

  SELECT 272,'1.42'

  UNION ALL

  SELECT 273,'1.43'

  UNION ALL

  SELECT 274,'1.5'

  UNION ALL

  SELECT 275,'1.6'

  UNION ALL

  SELECT 276,'5.0D1'

  UNION ALL

  SELECT 277,'4.0'

  UNION ALL

  SELECT 278,'4.0D'

  UNION ALL

  SELECT 279,'4.0E'

  UNION ALL

  SELECT 280,'5.0E1'

  UNION ALL

  SELECT 281,'3.1'

  UNION ALL

  SELECT 282,'2.5'

  UNION ALL

  SELECT 283,'2.0G'

  UNION ALL

  SELECT 284,'2.7A'

  UNION ALL

  SELECT 285,'3.0'

  UNION ALL

  SELECT 286,'3.01'

  UNION ALL

  SELECT 287,'3.0C'

  UNION ALL

  SELECT 288,'3.0D'

  UNION ALL

  SELECT 289,'3.0E'

  UNION ALL

  SELECT 290,'3.0F'

  UNION ALL

  SELECT 291,'3.0G'

  UNION ALL

  SELECT 292,'@@2.0F'

  UNION ALL

  SELECT 293,'@3.1.1A'

  UNION ALL

  SELECT 294,'10.1.@'

  I need to sort it

  Descending by the first number before the ., and then by the second number after the first . in a numeric sorting order. When I have a non-numeric value I need to ignore it and process the numeric portion before the .

  i.e. the results should look like this

  VERSIONVAL

  ------------------

  10.1.@

  9.3

  9.2

  9.1

  ...

  @3.1.1A

  3.0

  @@2.0F

  etc...

  Looking for something that works first and foremost. Preferably both on SQL 2000 and SQL 2k5+

  Best I've come up with so far is below... and it fails on the @@ value

  select

  *

  from #TEST

  ORDER BY

  CAST(

  CASE

  WHEN CHARINDEX('.',versionval) = 0 AND ISNUMERIC(versionval) =1 THEN versionval

  WHEN CHARINDEX('.',versionval) = 0 AND ISNUMERIC(versionval) = 0 THEN RIGHT(versionval,LEN(versionval)-CHARINDEX('@',versionval))

  WHEN ISNUMERIC(left(versionval,charindex('.',versionval)-1))=1 THEN left(versionval,charindex('.',versionval)-1)

  ELSE RIGHT(left(versionval,charindex('.',versionval)-1),LEN(left(versionval,charindex('.',versionval)-1))-1) END AS NUMERIC(10,5)) DESC,

  pubdata..agn_versions.versionval desc

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  November 18, 2009 at 2:46 pm

  #1081233

  not fully understanding

  are u saying 10.1.@ would come before 17?

• bc_

  Hall of Fame

  Points: 3111

  More actions

  November 18, 2009 at 3:40 pm

  #1081269

  Not sure I follow, but what if you got rid of the @'s for sorting:

  select

  *, REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))

  WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')

  ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END

  from #TEST

  ORDER BY CAST(CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))

  WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')

  ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END AS INT) DESC

  , REPLACE(REPLACE(versionval, '@', ''), '@@', '') DESC

  [font="Arial Narrow"]bc[/font]

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 19, 2009 at 6:54 am

  #1081518

  john scott miller (11/18/2009)

  ---

  not fully understanding

  are u saying 10.1.@ would come before 17?

  No... it's not a character comparison... as I said, my query attempt is only semi-working at this point.

  Whatever number comes before the first decimal point is the sorted numerically descending, with the number(s) after the remaining deimal point(s) also sorted descending.

  So 17 comes first

  17

  10.4.1

  10.3

  10.2.9

  10.2.1

  10.1

  @3.1

  %2.5

  etc.

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 19, 2009 at 6:54 am

  #1081520

  bc_ (11/18/2009)

  ---

  Not sure I follow, but what if you got rid of the @'s for sorting:

  select

  *, REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))

  WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')

  ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END

  from #TEST

  ORDER BY CAST(CASE CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', ''))

  WHEN 0 THEN REPLACE(REPLACE(versionval, '@', ''), '@@', '')

  ELSE LEFT(REPLACE(REPLACE(versionval, '@', ''), '@@', ''), CHARINDEX('.', REPLACE(REPLACE(versionval, '@', ''), '@@', '')) - 1) END AS INT) DESC

  , REPLACE(REPLACE(versionval, '@', ''), '@@', '') DESC

  That might work... I suppose if I removed everything else that's in the ASCII table but numbers and periods... hmmm

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• Rob Goddard

  SSCrazy

  Points: 2622

  More actions

  November 19, 2009 at 7:28 am

  #1081549

  Hi Mark,

  So it works in 2000 I've had to do away with CTEs so this looks a little convoluted. This looks to work on your test set?

  It's a starting point, sort of building the string slicing as I went, so I dare say there will be a way of taking this and building in some clever pattern matching etc.

  select * from #test

  order by

  cast(substring(replace(coalesce(left(versionval,nullif(charindex('.',versionval),0)),versionval),'.',''),patindex('%[0-9]%',replace(coalesce(left(versionval,nullif(charindex('.',versionval),0)),versionval),'.','')),50) as int) desc,

  cast(left(right(rtrim(versionval),len(versionval) - nullif(charindex('.',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - charindex('.',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - charindex('.',versionval))))) as int) desc

  --------
  [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 19, 2009 at 7:36 am

  #1081561

  i thought maybe using the PARSENAME function might get you to where you wanted...tis is still sorting as text, we could change that a bit, but i thought this was pretty close to what you wanted?

  select * ,

  PARSENAME(VERSIONVAL,4),

  PARSENAME(VERSIONVAL,3),

  PARSENAME(VERSIONVAL,2),

  PARSENAME(VERSIONVAL,1)

  from #TEST

  order by

  --to use parsename, we need to assume(or force) three periods exist:

  CASE

  WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) --no periods at all

  THEN PARSENAME(VERSIONVAL,1)

  WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 1 --one period

  THEN PARSENAME(VERSIONVAL,2)

  WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 2 --two periods

  THEN PARSENAME(VERSIONVAL,3)

  WHEN LEN(VERSIONVAL) = LEN(REPLACE(VERSIONVAL,'.','')) + 3 --three periods

  THEN PARSENAME(VERSIONVAL,4)

  END

  DESC

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 19, 2009 at 7:39 am

  #1081565

  Hey Rob,

  That actually works awesomely for 858/860 of my records in production.

  I didn't realize how bad this data was until I found two records it doesn't work on. (I tested it against the rest of the data by excluding the records in question with a where clause).

  So let's add those as well.

  INSERT INTO #TEST(TESTID,VERSIONVAL)

  SELECT 498,'CNXN'

  UNION ALL

  SELECT 670,'8,1.0'

  So I checked with the developer working on this, and got the following rule for CNXN Values without numeric values should be sorted alphabetically at the end.

  and commas should be treated as decimal points/periods.

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 19, 2009 at 8:21 am

  #1081591

  How about adding a

  CASE WHEN PATINDEX('%[0-9]%',VERISIONVAL) = 0 THEN 1 ELSE 0 END,

  In fron

  t of the other ORDER BY criteria (Order by anything that has a number first, then the things that don't follow alphabeticaly)

  And then use a REPLACE(VERSIONVAL,',','.') to sub out the period for your comma inside that order by to handle those two special situations.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Rob Goddard

  SSCrazy

  Points: 2622

  More actions

  November 19, 2009 at 8:26 am

  #1081597

  You're right, and looking at it closer the code is actually putting 6.10 as a more recent version than 6.3 (10 > 3 in the way the logic is done)

  This code here should sort out the alpha only (and give a final sort to versions with letter like 5.1E and 5.1B), and the version minor number issue.

  I've altered the charindex search for the point to a patindex search for the first comma or point, does this look right on your data?

  select * from #test

  order by

  cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),50) as int) desc,

  cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,

  versionval desc

  --------
  [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 19, 2009 at 8:38 am

  #1081610

  So far so good... except the CNXN doesn't work still.

  Msg 245, Level 16, State 1, Line 1

  Conversion failed when converting the varchar value 'CNXN' to data type int.

  Originally I wrote a query to build the insert query... of course my results screen doesn't have enough room to take it. and all the good ones were dropped out. 🙁

  I've also got other bizarre ones like 8.X, 9.0CBT and 9.0 STM

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• Rob Goddard

  SSCrazy

  Points: 2622

  More actions

  November 19, 2009 at 8:54 am

  #1081630

  mtassin (11/19/2009)

  ---

  So far so good... except the CNXN doesn't work still.

  😀

  OK, that's my fault again. You did mention that one and I didn't add it to the test data set... How's this one looking?

  select *

  from #test

  order by

  cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),nullif(patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),0),50) as int) desc,

  cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,

  versionval desc

  --------
  [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

• jcrawf02

  SSC-Insane

  Points: 24198

  More actions

  November 20, 2009 at 6:45 am

  #1082174

  What is this sort order doing? Other than giving you fits?

  ---------------------------------------------------------
  How best to post your question[/url]
  How to post performance problems[/url]
  Tally Table:What it is and how it replaces a loop[/url]

  "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 20, 2009 at 7:01 am

  #1082185

  jcrawf02 (11/20/2009)

  ---

  What is this sort order doing? Other than giving you fits?

  It's going into a web based drop down combo/list box for users to select the version of software that was reported, licensed, etc.

  Granted the versions are filthy... they should be managed as something consistent like version.release.build, and those should all be in a table for versions with an FK back to the table for the versions. What I get is a table in the database that predates me that was a single varchar(20) field that users could type anything into.

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]

• mtassin

  SSC-Insane

  Points: 23099

  More actions

  November 20, 2009 at 7:04 am

  #1082190

  Rob Goddard (11/19/2009)

  ---

  mtassin (11/19/2009)

  ---

  So far so good... except the CNXN doesn't work still.

  😀

  OK, that's my fault again. You did mention that one and I didn't add it to the test data set... How's this one looking?

  select *

  from #test

  order by

  cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),nullif(patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),0),50) as int) desc,

  cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,

  versionval desc

  This works perfectly! Wow... I've used every one of those statements above, but I don't think I've ever used them all in the same field calculation.

  ---

  
  --Mark Tassin
  MCITP - SQL Server DBA
  Proud member of the Anti-RBAR alliance.
  For help with Performance click this link[/url]
  For tips on how to post your problems[/url]