-- PlaceReverse.sql
/*
2017-04-20 Tom Holden ve3meo
Creates a temporary SQLite View of the Standardized Place names 
in reverse order with up to three commas in the Place name.
*/

-- PlaceCommaParse.sql
/*
2013-02-17 Tom Holden ve3meo
Creates a temporary View 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 VIEW IF EXISTS xPlaceCommaView;
 
CREATE TEMP VIEW xPlaceCommaView 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 ''
        )
    );
    
    -- 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 VIEW IF EXISTS xPlacePartsView;
 
CREATE TEMP VIEW xPlacePartsView 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 + 2, Comma2 - Comma1 - 2)
        WHEN Comma1 > 0
            THEN SUBSTR(Normalized, Comma1 + 2)
        ELSE ''
        END AS Place2
    ,CASE
        WHEN Comma3 > Comma2
            THEN SUBSTR(Normalized, Comma2 + 2, Comma3 - Comma2 - 2)
        WHEN Comma2 > Comma1
            THEN SUBSTR(Normalized, Comma2 + 2)
        ELSE ''
        END AS Place3
    ,CASE
        WHEN Comma3 > Comma2
            THEN SUBSTR(Normalized, Comma3 + 2)
        ELSE ''
        END AS Place4
FROM xPlaceCommaView;

-- Reverse Names
DROP VIEW IF EXISTS xPlaceReverseView
;
CREATE TEMP VIEW xPlaceReverseView
AS
SELECT 
  PlaceID
  ,  Place4 || SUBSTR(', ', 1, LENGTH(Place4))
  || Place3 || SUBSTR(', ', 1, LENGTH(Place3)) 
  || Place2 || SUBSTR(', ', 1, LENGTH(Place2)) || Place1
  AS PlaceReverse
FROM xPlacePartsView
;

SELECT * FROM xPlaceReverseView
;