-- PlaceParse.sql   
/*
   2013-02-17 Tom Holden ve3meo
   
   Requires existence of table created by PlaceCommaParse.sql.
   Extracts the parts of a 4-part Standardized Place name and saves them
   to a temporary table    
   */
DROP TABLE IF EXISTS xPlacePartsTable;

CREATE TEMP TABLE xPlacePartsTable AS
SELECT *
	,CASE 
		WHEN Comma1 > 0
			THEN SUBSTR(Normalized, 1, Comma1 - 1)
		ELSE Normalized
		END AS Place1
	,CASE 
		WHEN Comma2 > Comma1
			THEN SUBSTR(Normalized, Comma1 + 1, Comma2 - Comma1 - 1)
		WHEN Comma1 > 0
			THEN SUBSTR(Normalized, Comma1 + 1)
		ELSE ''
		END AS Place2
	,CASE 
		WHEN Comma3 > Comma2
			THEN SUBSTR(Normalized, Comma2 + 1, Comma3 - Comma2 - 1)
		WHEN Comma2 > Comma1
			THEN SUBSTR(Normalized, Comma2 + 1)
		ELSE ''
		END AS Place3
	,CASE 
		WHEN Comma3 > Comma2
			THEN SUBSTR(Normalized, Comma3 + 1)
		ELSE ''
		END AS Place4
FROM xPlaceCommaTable;