CODEWITHSUNDEEP
sqlsql-server
AShah
AShah

Reputation: 942

sort by second string in database field

I have the below sql statement which sorts an address field (address1) using the street name not the number. This seems to work fine but I want the street names to appear alphabetically. The ASC at the end of order by doesnt help e.g Address1 field might contain "5 Elm Close" - a normal sort and order will sort by the number the below will sort by looking at the 2nd string "Elm" (Using SQL Server)

SELECT tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, 
tbladdress.AddressLine1, tbladdress.AddressLine2 
FROM tblcontact 
INNER JOIN tbladdress 
  ON tblcontact.AddressID = tbladdress.AddressID 
LEFT JOIN tblDonate 
  ON tblcontact.ContactID = tblDonate.ContactID 
WHERE (tbladdress.CollectionArea = 'Queens Park')
GROUP BY tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, 
tbladdress.AddressLine1, tbladdress.AddressLine2
ORDER BY REVERSE(LEFT(REVERSE(tbladdress.AddressLine1), 
            charindex(' ', REVERSE(tbladdress.AddressLine1)+' ')-1)) asc

Gordon's statement sorts as below

1 Kings Road
10 Olivier Way
11 Albert Street
11 Kings Road
11 Princes Road
120 High Street

Upvotes: 0

Views: 87

Answers (4)

AShah
AShah

Reputation: 942

This is the bit of code that works in sql server

order by (case when tbladdress.AddressLine1 like '[0-9]% %'
then substrING(tbladdress.AddressLine1, charindex(' ', tbladdress.AddressLine1) + 1, len(tbladdress.AddressLine1))
else tbladdress.AddressLine1
end)

Upvotes: 0

AHiggins
AHiggins

Reputation: 7227

Try this: I based it off of Gordon's code, but altered it to remove the LEFT(AddressLine1, 1) portion - a single-character string could never be match the pattern "n + space + %".

This works on my SQL-Server 2012 environment:

WITH tbladdress AS 
  (
    SELECT AddressLine1 FROM (VALUES ('1 Kings Road'),('10 Olivier Way'), ('11 Albert Street')) AS V(AddressLine1)
  )

SELECT 
    AddressLine1
FROM tbladdress 
order by (case when tbladdress.AddressLine1 like '[0-9]% %'
               then substrING(tbladdress.AddressLine1, charindex(' ', tbladdress.AddressLine1) + 1, len(tbladdress.AddressLine1))
               else tbladdress.AddressLine1
          end)

This is edited to be more similar to Gordon's code (position of closing parentheses, substr instead of substring):

order by (case when tbladdress.AddressLine1 like '[0-9]% %'
               then substr(tbladdress.AddressLine1, charindex(' ', tbladdress.AddressLine1) + 1), len(tbladdress.AddressLine1)
               else tbladdress.AddressLine1
          end)

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32230

I don't think you need to use REVERSE() at all. That seems like a trap.

ORDER BY 
    CASE
        WHEN ISNUMERIC(LEFT(tbladdress.AddressLine1,CHARINDEX(' ',tbladdress.AddressLine1) - 1))
        THEN RIGHT(tbladdress.AddressLine1,LEN(tbladdress.AddressLine1) - CHARINDEX(' ',tbladdress.AddressLine1))
        ELSE tbladdress.AddressLine1
    END,
    CASE 
        WHEN ISNUMERIC(LEFT(tbladdress.AddressLine1,CHARINDEX(' ',tbladdress.AddressLine1) - 1))
        THEN CAST(LEFT(tbladdress.AddressLine1,CHARINDEX(' ',tbladdress.AddressLine1) - 1) AS INT)
        ELSE NULL
    END

Also, you have a GROUP BY with no aggregate function. While that's not wrong, per se, it is weird. Just use DISTINCT if you're getting duplicate records.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you assume that the street name is the first or second value in a space separated string, you could try:

order by (case when left(tbladdress.AddressLine1, 1) like '[0-9]% %'
               then substr(tbladdress.AddressLine1, charindex(' ', tbladdress.AddressLine1) + 1), len(tbladdress.AddressLine1) )
               else tbladdress.AddressLine1
          end)

Upvotes: 1

Related Questions