How to change toggle for some words in a string

I am tidying up my Ancestry tree, and have access to the SQL using SQLite.

I would like to standardize the descriptions I have for their census information.

For example I have

1. Relationship: Wife. Occupation: Unpaid domestic duties.
2. Relationship: Wife. Occupation: Unpaid domestic Duties.
3. Relationship: Wife. Occupation: Unpaid Domestic Duties.

Is there a simple SQL instruction I can use so that any element of the string after "Occupation" is set to 1st word capital, then the remainder as lower case (i.e. the 1st offering).

I would then like to offer this for the words between Relationship and Occupation (e.g. Brother in law, Brother in Law, Brother In Law).

Thank you for your help.

I've not tried any as I don't have the knowledge and can't find what I'm looking for through Google

In short NO there is not a simple instruction.

You could utilise some useful SQLite inbuilt functions (as per https://www.sqlite.org/lang_corefunc.html), notably substr, upper and lower.

Consider the following demo (which assumes that the table is named x and there are two columns, one for the relationship and another for the occupation)

The demo includes 3 queries, the first just extracting the corrected data, the second introducing how that can be used to drive a CTE (temporary table) and the third showing how the CTE can be used to drive an UPDATE of the actual table (table x):-

DROP TABLE IF EXISTS x;
CREATE TABLE IF NOT EXISTS x (id INTEGER PRIMARY KEY, relationship TEXT, occupation TEXT);

/* Add some data */
INSERT INTO x (relationship,occupation) VALUES
    ('Relationship: Wife','Occupation: Unpaid domestic duties'),
    ('Relationship: Wife','Occupation: Unpaid domestic Duties'),
    ('Relationship: Wife','Occupation: Unpaid Domestic Duties'),
    ('Relationship: whatever','Occupation: whatever'),
    ('Relationship: brother in law','Occupation: Unpaid Domestic Duties'),
    ('Relationship: BROTHER IN LAW','Occupation: Unpaid Domestic Duties'),
    ('','') /* To show result of empty*/,
    (null,null) /* To show result of nulls*/
;

/* OUTPUT 1 */
/* Query to just extract the data */
SELECT 
    *, /* ALL rows*/
    /* PLUS a new column for the modified/corrected relationship column */
    'Relationship: '
        || upper(substr(relationship,1 + length('Relationship: '),1))
        || lower(substr(relationship,2 + length('Relationship: '))) 
    AS newR,
    /* PLUS a new column for the modified/corrected occupation column */
    'Occupation: '
        || upper(substr(occupation,1 + length('Occupation: '),1))
        || lower(substr(occupation,2 + length('Occupation: ')))
    AS  newO
FROM x;

/* OUTPUT 2 */
/* As above but as a CTE (Common Table Expression aka a temporary table) */
WITH
 cte AS (
    SELECT 
        *,
        'Relationship: '
            || upper(substr(relationship,1 + length('Relationship: '),1))
            || lower(substr(relationship,2 + length('Relationship: '))) 
        AS newR,
        'Occupation: '
            || upper(substr(occupation,1 + length('Occupation: '),1))
            || lower(substr(occupation,2 + length('Occupation: ')))
        AS  newO
    FROM x
 )
SELECT * FROM cte
;
 
 /* As above but as a CTE (Common Table Expression aka a temporary table) */
WITH
 cte AS (
        SELECT 
        *,
        'Relationship: '
            || upper(substr(relationship,1 + length('Relationship: '),1))
            || lower(substr(relationship,2 + length('Relationship: '))) 
        AS newR,
        'Occupation: '
            || upper(substr(occupation,1 + length('Occupation: '),1))
            || lower(substr(occupation,2 + length('Occupation: ')))
        AS  newO
    FROM x
 )
UPDATE x SET relationship = (SELECT newR FROM cte WHERE x.id=cte.id), occupation = (SELECT newO FROM cte WHERE x.id=cte.id)
 ;
 /** 3rd OUTPUT i.e. the updated/corrected table */
SELECT * FROM x;

/* Cleanup the demo environment */
DROP TABLE IF EXISTS x;

• if the relationship and occupation are in a single column they the above could be used for the basis but would need modifying to suit.

The 3 outputs are as follows:-