select house_no order

 Author Message shashianireddy Mr or Mrs. 500 Group: General Forum Members Points: 514 Visits: 135 k ..thanku if house no like...SELECT '3-4-53 . HYDERABAD' UNION ALL SELECT '3-400-199/1.ASVV' UNION ALL SELECT '3-49-vizag' UNION ALL SELECT '3-53-22,nagar' UNION ALL SELECT '4-5-1300.old colony' UNION ALL SELECT '4-500-120' UNION ALL SELECT '3-4-' UNION ALL SELECT '2/4' UNION ALL SELECT '2-4' UNION ALL SELECT '3-5098-hasd' UNION ALL SELECT '3-6van' UNION ALL SELECT '4-5-123asvr'after 1st "-" 2nd ''-'' if number is two or more digits not display in order wise.plz write quarie if digits two or more display in order wise shashianireddy Mr or Mrs. 500 Group: General Forum Members Points: 514 Visits: 135 if ex - table like this below format using ur quarie not display order WITH MyAddresses (HouseNo) AS ( -- meagre sample data set SELECT '3-12-19 . HYDERABAD' UNION ALL SELECT '3-1-900' UNION ALL SELECT '3-1-99/3' UNION ALL SELECT '3-5-vizag' UNION ALL SELECT '4-5-123asvr')SELECT a.*, x.*, y.*, z.* FROM MyAddresses a-- get the position of the hyphensCROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2CROSS 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)) xCROSS APPLY ( -- calculate the length of the numeric - first - part of element 3 SELECT n = NULLIF(MIN(n),1) 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(Element3,n,1) NOT LIKE '[0-9]') yCROSS APPLY ( -- jig around with the elements to force the desired sort order SELECT Element1 = CASE WHEN Element1 NOT LIKE '%/%' THEN Element1+CHAR(255) ELSE Element1 END, Element2, Element3 = CASE WHEN y.n IS NULL THEN Element3 ELSE CHAR(255)+RIGHT('000000'+LEFT(Element3,y.n),6) END) zORDER BY z.Element1, z.Element2, z.Element3out put like below 3-1-9003-1-99/33-12-19 . HYDERABAD3-5-vizag4-5-123asvrif after first & second '-' more than two digits display order ChrisM@Work SSC Guru Group: General Forum Members Points: 97803 Visits: 20721 shashianireddy (12/20/2013)k ..thanku if house no like...SELECT '3-4-53 . HYDERABAD' UNION ALL SELECT '3-400-199/1.ASVV' UNION ALL SELECT '3-49-vizag' UNION ALL SELECT '3-53-22,nagar' UNION ALL SELECT '4-5-1300.old colony' UNION ALL SELECT '4-500-120' UNION ALL SELECT '3-4-' UNION ALL SELECT '2/4' UNION ALL SELECT '2-4' UNION ALL SELECT '3-5098-hasd' UNION ALL SELECT '3-6van' UNION ALL SELECT '4-5-123asvr'after 1st "-" 2nd ''-'' if number is two or more digits not display in order wise.plz write quarie if digits two or more display in order wiseNo problem. Left-pad the second element with a string of '0's then take a fixed number of chars from the right-hand side, like this:`;WITH MyAddresses (HouseNo) AS ( -- meagre sample data set SELECT '3-4-53 . HYDERABAD' UNION ALL SELECT '3-400-199/1.ASVV' UNION ALL SELECT '3-49-vizag' UNION ALL SELECT '3-53-22,nagar' UNION ALL SELECT '4-5-1300.old colony' UNION ALL SELECT '4-500-120' UNION ALL SELECT '3-4-' UNION ALL SELECT '2/4' UNION ALL SELECT '2-4' UNION ALL SELECT '3-5098-hasd' UNION ALL SELECT '3-6van' UNION ALL SELECT '4-5-123asvr')SELECT a.*, x.*, y.*, z.* FROM MyAddresses a-- get the position of the hyphensCROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2CROSS 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)) xCROSS APPLY ( -- calculate the length of the numeric - first - part of element 3 SELECT n = NULLIF(MIN(n),1) 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(Element3,n,1) NOT LIKE '[0-9]') yCROSS APPLY ( -- jig around with the elements to force the desired sort order SELECT Element1 = CASE WHEN Element1 NOT LIKE '%/%' THEN Element1+CHAR(255) ELSE Element1 END, Element2 = RIGHT('000000'+Element2,6), -- Changed 20131220 Element3 = CASE WHEN y.n IS NULL THEN Element3 ELSE CHAR(255)+RIGHT('000000'+LEFT(Element3,y.n),6) END) zORDER 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps shashianireddy Mr or Mrs. 500 Group: General Forum Members Points: 514 Visits: 135 if houseno like 3-1-900 ,3-1-92/4 ,.......not display order output like3-1-9003-1-92/4 hyd3-1-99/7using.......WITH MyAddresses (HouseNo) AS ( -- meagre sample data setSELECT '3-1-900' UNION ALLSELECT '3-1-92/4 hyd' UNION ALLSELECT '3-1-99/7' )SELECT a.*, x.*, y.*, z.* FROM MyAddresses a-- get the position of the hyphensCROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2CROSS 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)) xCROSS APPLY ( -- calculate the length of the numeric - first - part of element 3 SELECT n = NULLIF(MIN(n),1) 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(Element3,n,1) NOT LIKE '[0-9]') yCROSS APPLY ( -- jig around with the elements to force the desired sort order SELECT Element1 = CASE WHEN Element1 NOT LIKE '%/%' THEN Element1+CHAR(255) ELSE Element1 END, Element2 = RIGHT('000000'+Element2,6), Element3 = CASE WHEN y.n IS NULL THEN Element3 ELSE CHAR(255)+RIGHT('000000'+LEFT(Element3,y.n),6) END)zORDER BY z.Element1, z.Element2, z.Element3 shashianireddy Mr or Mrs. 500 Group: General Forum Members Points: 514 Visits: 135 if houseno like 3-1-900 ,3-1-92/4 ,.......not display order output like3-1-9003-1-92/4 hyd3-1-99/7using.......WITH MyAddresses (HouseNo) AS ( -- meagre sample data setSELECT '3-1-900' UNION ALLSELECT '3-1-92/4 hyd' UNION ALLSELECT '3-1-99/7' )SELECT a.*, x.*, y.*, z.* FROM MyAddresses a-- get the position of the hyphensCROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2CROSS APPLY ( -- split the elements from the stringSELECT 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)) xCROSS APPLY ( -- calculate the length of the numeric - first - part of element 3SELECT n = NULLIF(MIN(n),1) FROM (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)) tWHERE SUBSTRING(Element3,n,1) NOT LIKE '[0-9]') yCROSS APPLY ( -- jig around with the elements to force the desired sort orderSELECTElement1 = CASE WHEN Element1 NOT LIKE '%/%' THEN Element1+CHAR(255) ELSE Element1 END, Element2 = RIGHT('000000'+Element2,6), Element3 = CASE WHEN y.n IS NULL THEN Element3 ELSE CHAR(255)+RIGHT('000000'+LEFT(Element3,y.n),6) END)zORDER BY z.Element1, z.Element2, z.Element3 ChrisM@Work SSC Guru Group: General Forum Members Points: 97803 Visits: 20721 `;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 hyphensCROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2CROSS 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)) xCROSS 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]') yCROSS 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 ) zORDER 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps