Place names in reverse order

From TNG_Wiki
Jump to navigation Jump to search

This report was initially developed by Warren S Gilbert on request of Bruce Roy. It gives a list of place names sorted on the biggest entity (most of the times the country). Then it sorts on province or state, then county or municipality and finally the cities. If you have also street names listed, this one will still work. In case you want to refine it even more, you will have to add

=====

,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) = 
TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) )
End

=

at the end

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate


SELECT place
FROM tng_places
ORDER BY
CASE WHEN LOCATE( ',', place ) =0
THEN place
ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) )
END ,
CASE WHEN LOCATE( ',', place ) =0
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = 
TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = 
TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = 
TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
End