select house_no order

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 20, 2013 at 4:22 am

  #1675095

  ;WITH MyAddresses (HouseNo) AS ( -- meagre sample data set

  SELECT '2/4' UNION ALL

  SELECT '2-4' UNION ALL

  SELECT '3-1-92/4 hyd' UNION ALL

  SELECT '3-1-99/7' UNION ALL

  SELECT '3-1-900' UNION ALL

  SELECT '3-4-' UNION ALL

  SELECT '3-4-53 . HYDERABAD' UNION ALL

  SELECT '3-49-vizag' UNION ALL

  SELECT '3-53-22,nagar' UNION ALL

  SELECT '3-400-199/1.ASVV' UNION ALL

  SELECT '3-5098-hasd' UNION ALL

  SELECT '3-6van' UNION ALL

  SELECT '4-5-123asvr' UNION ALL

  SELECT '4-5-1300.old colony' UNION ALL

  SELECT '4-500-120'

  )

  SELECT a.*, x.*, y.*, z.*

  FROM MyAddresses a

  -- get the position of the hyphens

  CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1

  CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2

  CROSS APPLY ( -- split the elements from the string

  SELECT

  Element1 = LEFT(a.HouseNo,ISNULL(p1.n-1,8000)),

  Element2 = SUBSTRING(a.HouseNo,p1.n+1,ISNULL(p2.n-p1.n-1,8000)),

  Element3 = SUBSTRING(a.HouseNo,p2.n+1,8000)

  ) x

  CROSS APPLY ( -- calculate the length of the numeric - first - part of element 3

  SELECT n = NULLIF(MIN(n),1)-1 -- Changed 20131220

  FROM (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)) t

  WHERE SUBSTRING(x.Element3,n,1) NOT LIKE '[0-9]'

  ) y

  CROSS APPLY ( -- jig around with the elements to force the desired sort order

  SELECT

  Element1 = CASE WHEN x.Element1 NOT LIKE '%/%' THEN x.Element1+CHAR(255) ELSE x.Element1 END,

  Element2 = RIGHT('000000'+x.Element2,6), -- Changed 20131220

  Element3 = CASE

  WHEN y.n IS NULL THEN x.Element3

  ELSE CHAR(255)+RIGHT('000000'+LEFT(x.Element3,y.n),6)

  END

  ) z

  ORDER BY z.Element1, z.Element2, z.Element3

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden