--NickGivenSplit.sql
/*
2015-12-30 Tom Holden ve3meo
Finds Given names with quotation marks and extracts what follows the first
and puts it into the Nickname field. Given cleared of everything to right of and including
the first quotation mark. Undoes what NickGivenCombine.sql does.

Warning, do not use if there are Givens with more than one pair of quotation marks or if the 
quoted Nickname is not after all the Given names. 
*/
DROP VIEW IF EXISTS GivenNickSplit
;
CREATE TEMP VIEW GivenNickSplit AS
SELECT NameID
     , TRIM(SUBSTR(Given, 1, INSTR(Given,'"')-1)) AS Given 
     , TRIM(REPLACE(SUBSTR(Given,INSTR(Given,'"')),'"','')) AS NickName FROM NameTable
WHERE INSTR(Given,'"')   --a quoted nickname is embedded in Given
AND NOT LENGTH(Nickname) --the NickName field is empty
ORDER BY NameID
;
UPDATE NameTable 
SET Given = (SELECT Given FROM GivenNickSplit GNS WHERE NameTable.NameID = GNS.NameID)
   ,NickName = (SELECT NickName FROM GivenNickSplit GNS WHERE NameTable.NameID = GNS.NameID)
WHERE NameID IN (SELECT NameID FROM GivenNickSplit)
;