Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

select house_no order Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 3:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
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
Post #1524911
Posted Friday, December 20, 2013 3:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
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 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)
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]'
) y

CROSS 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
) z
ORDER BY z.Element1, z.Element2, z.Element3


out put like below

3-1-900
3-1-99/3
3-12-19 . HYDERABAD
3-5-vizag
4-5-123asvr



if after first & second '-' more than two digits display order

Post #1524914
Posted Friday, December 20, 2013 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 6,863, Visits: 14,161
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 wise


No 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 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)
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]'
) y

CROSS 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
) 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1524915
Posted Friday, December 20, 2013 4:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
if houseno like 3-1-900 ,3-1-92/4 ,.......not display order

output like
3-1-900
3-1-92/4 hyd
3-1-99/7

using.......
WITH MyAddresses (HouseNo) AS ( -- meagre sample data set

SELECT '3-1-900' UNION ALL
SELECT '3-1-92/4 hyd' UNION ALL
SELECT '3-1-99/7'

)
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)
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]'
) y

CROSS 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
)z
ORDER BY z.Element1, z.Element2, z.Element3
Post #1524917
Posted Friday, December 20, 2013 4:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
if houseno like 3-1-900 ,3-1-92/4 ,.......not display order

output like
3-1-900
3-1-92/4 hyd
3-1-99/7

using.......
WITH MyAddresses (HouseNo) AS ( -- meagre sample data set

SELECT '3-1-900' UNION ALL
SELECT '3-1-92/4 hyd' UNION ALL
SELECT '3-1-99/7'

)
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)
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]'
) y

CROSS 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
)z
ORDER BY z.Element1, z.Element2, z.Element3
Post #1524923
Posted Friday, December 20, 2013 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 6,863, Visits: 14,161
;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
Exploring Recursive CTEs by Example Dwain Camps
Post #1524925
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse