-- PlaceCommaParse.sql
/*
2013-02-17 Tom Holden ve3meo
Creates a temporary table of non-empty Standardized Place names with the
positions of up to three commas in the string. Can be used to parse out
the 4 parts of the name for further use such as the generation of a 2-part 
Abbreviation and 3-part Name for reports. 
*/
DROP TABLE IF EXISTS xPlaceCommaTable;
	
CREATE TEMP TABLE xPlaceCommaTable AS
SELECT PlaceID
	,Normalized
	,Comma1
	,Comma2
	,Comma2 + INSTR(SUBSTR(Normalized, Comma2 + 1), ',') AS Comma3
FROM (
	SELECT PlaceID
		,Normalized
		,Comma1
		,Comma1 + INSTR(SUBSTR(Normalized, Comma1 + 1), ',') AS Comma2
	FROM (
		SELECT PlaceID
			,Normalized
			,INSTR(Normalized, ',') AS Comma1
		FROM PlaceTable
		WHERE PlaceType = 0
			AND Normalized NOT LIKE ''
		)
	);