/*
_update_census_birth_places.sql    Jerry Bryan   16 Nov 2024

Converts strings such as TN NC VA in my census notes to strings such
as born TN, father born NC, mother born VA. I use year specific census
facts such as 1860 Census and 1870 Census rather than the standard Census
fact. The update is therefore restricted to those year specific census facts
out of concern that similar strings may appear in notes for other fact types.
The signature to define the strings that need to be changed is the regular
expression [A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]

This script is quite specific to the way I enter my census transcriptions.
The possible value to other users is therefore as an example of the use
of regular expressions in SQLite scripts for the RM database.

The script will only convert one string per note, and a given note may contain
more than one string that needs to be converted. So I simply execute the script
multiple times until there are no more strings that need to be converted.
*/

UPDATE EventTable
SET Note =

REPLACE(Note,

-- This is the original string, such as TN NC VA. Convert it temporarily to XX XX XX and
-- then find the location of the XX XX XX. Otherwise, there is not a good way to find
-- where the original string begins since we don't know what we are looking for.
SUBSTR(Note,INSTR(regexp_replace(Note,'[A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]','XX XX XX'),'XX XX XX'),8),

-- This is the replacement string, such as born TN, father born NC, mother born VA. It is the
-- original string with added text. The calculation of the replacement string also requires finding
-- the original string, and it also operates by converting the orginal string temporarily to XX XX XX.
'born '
|| SUBSTR(SUBSTR(Note,INSTR(regexp_replace(Note,'[A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]','XX XX XX'),'XX XX XX'),8),1,2)
|| ', father born '
|| SUBSTR(SUBSTR(Note,INSTR(regexp_replace(Note,'[A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]','XX XX XX'),'XX XX XX'),8),4,2)
|| ', mother born '
|| SUBSTR(SUBSTR(Note,INSTR(regexp_replace(Note,'[A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]','XX XX XX'),'XX XX XX'),8),7,2)

)

WHERE EventType IN
(
SELECT FT.FactTypeID
FROM FactTypeTable AS FT
WHERE FT.Name LIKE '%census%'      -- only update the note in census facts such as 1860 Census, 1870 Census, etc.
)
AND Note REGEXP '[A-Z][A-Z] [A-Z][A-Z] [A-Z][A-Z]'   -- only update notes that need to be updated