SET NAMES utf8mb4;

UPDATE md_clients
SET
    cnp = CASE
        WHEN (
            cnp IS NULL OR cnp = ''
        )
        AND CHAR_LENGTH(
            REGEXP_REPLACE(COALESCE(tax_id, ''), '[^0-9]', '')
        ) = 13
        THEN REGEXP_REPLACE(COALESCE(tax_id, ''), '[^0-9]', '')
        ELSE cnp
    END,
    cui = CASE
        WHEN (
            cui IS NULL OR cui = ''
        )
        AND COALESCE(tax_id, '') <> ''
        AND CHAR_LENGTH(
            REGEXP_REPLACE(COALESCE(tax_id, ''), '[^0-9]', '')
        ) <> 13
        THEN UPPER(
            REGEXP_REPLACE(COALESCE(tax_id, ''), '[^A-Za-z0-9]', '')
        )
        ELSE cui
    END,
    type = CASE
        WHEN CHAR_LENGTH(
            REGEXP_REPLACE(COALESCE(tax_id, ''), '[^0-9]', '')
        ) = 13
        THEN 'individual'
        WHEN COALESCE(tax_id, '') <> ''
        THEN 'company'
        ELSE type
    END
WHERE
    COALESCE(tax_id, '') <> ''
    AND (
        COALESCE(cnp, '') = ''
        OR COALESCE(cui, '') = ''
    );