/* Paragraph-strip.sql
   2011-12-22 by ve3meo
   Strips CR/LF and spaces from around Name and Event custom sentence templates and Person, Family, Name and Event Notes.
*/


-- Reiterate the following query until no further results to remove leading CRLF from the custom sentence
UPDATE EventTable SET Sentence = SUBSTR (Sentence,3,LENGTH(Sentence)) WHERE Sentence LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove leading CRLF from the Note
UPDATE EventTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove trailing CRLF from the Note
UPDATE EventTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT)
;
-- Reiterate the following query until no further results to remove leading CRLF from the Note
UPDATE PersonTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove trailing CRLF from the Note
UPDATE PersonTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT)
;
-- Reiterate the following query until no further results to remove leading CRLF from the Note
UPDATE FamilyTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove trailing CRLF from the Note
UPDATE FamilyTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT)
;
-- Reiterate the following query until no further results to remove leading CRLF from the custom sentence
UPDATE NameTable SET Sentence = SUBSTR (Sentence,3,LENGTH(Sentence)) WHERE Sentence LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove leading CRLF from the Note
UPDATE NameTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%'
;
-- Reiterate the following query until no further results to remove trailing CRLF from the Note
UPDATE NameTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT)
;
-- Remove leading and trailing spaces
UPDATE EventTable SET Sentence = TRIM(Sentence)
;
UPDATE EventTable SET NOTE = TRIM(Note)
;
UPDATE PersonTable SET NOTE = TRIM(Note)
;
UPDATE FamilyTable SET NOTE = TRIM(Note)
;
UPDATE NameTable SET Sentence = TRIM(Sentence)
;
UPDATE NameTable SET NOTE = TRIM(Note)
;