Some column base data modification

-- copy data from one column to another
UPDATE `stipend` SET parent_name_modified = parent_name_unicode;
-- view if properly copied
SELECT t.parent_name_unicode, t.parent_name_modified FROM `stipend_info_10JulDisb_1Jul-1` t;
-- replace a string by another string in a column
UPDATE `stipend_info_10JulDisb_1Jul-1` SET `parent_name_modified` = REPLACE(`parent_name_modified`,' বেগম','')
-- create a table by copying an existing table
create table `stipend_info_latest_experiment` as select * from `stipend_info`;


-- if a parent have valid name and nId and mismatched more than one phone number, compare them
select f.parent_name_unicode, f.nationalidoriginal, f.mobile_number, s.mobile_number from `stipend_info_10_11JulDis_2Jul_1` f
inner join `stipend_info_10_11JulDis_2Jul_1` s
on f.parent_name_unicode = s.parent_name_unicode and f.nationalidoriginal = s.nationalidoriginal
where f.mobile_number != s.mobile_number and f.nationalidoriginal!='Null'
and ( -- first mobiel valid
  (SUBSTR(f.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(f.`mobile_number`) = 11)
  OR
  (SUBSTR(f.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(f.`mobile_number`) = 10)
)
and not ( -- second mobile invalid
  (SUBSTR(s.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(s.`mobile_number`) = 11)
  OR
  (SUBSTR(s.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(s.`mobile_number`) = 10)
);

-- get the count of valid formatted mobile numbers against a parent name and national id
select count(distinct t.mobile_number) validMobileCount, Group_Concat(distinct t.mobile_number separator ',  ') from `stipend_info_10_11JulDis_2Jul_1` t
where
(
  (SUBSTR(t.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(t.`mobile_number`) = 11)
  OR
  (SUBSTR(t.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(t.`mobile_number`) = 10)
)
group by t.parent_name_unicode, t.nationalidoriginal
order by validMobileCount desc;

-- if for same parent name and nId if there is more than one mobile number then view the wrong and correct
-- there may be more than one proper formatted mobile number found. that's a problem. we will do if only one valid mobile number is found
SELECT
    f.parent_name_unicode,
    f.nationalidoriginal,
    f.mobile_number,
    s.mobile_number
FROM
    `stipend_info_10_11JulDis_2Jul_1` f
        INNER JOIN
    `stipend_info_10_11JulDis_2Jul_1` s ON f.parent_name_unicode = s.parent_name_unicode
        AND f.nationalidoriginal = s.nationalidoriginal
        INNER JOIN
    (SELECT
        COUNT(DISTINCT t.mobile_number) validMobileCount,
            t.mobile_number
    FROM
        `stipend_info_10_11JulDis_2Jul_1` t
    WHERE
        ((SUBSTR(t.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
            AND LENGTH(t.`mobile_number`) = 11)
            OR (SUBSTR(t.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
            AND LENGTH(t.`mobile_number`) = 10))
    GROUP BY t.parent_name_unicode , t.nationalidoriginal) t ON f.mobile_number = t.mobile_number
WHERE
    f.mobile_number != s.mobile_number
        AND f.nationalidoriginal != 'Null'
        AND ((SUBSTR(f.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(f.`mobile_number`) = 11)
        OR (SUBSTR(f.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(f.`mobile_number`) = 10))
        AND NOT ((SUBSTR(s.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(s.`mobile_number`) = 11)
        OR (SUBSTR(s.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(s.`mobile_number`) = 10));

-- if same (parent name, nId) have different mobile name and only one of them is in correct format replace the wrong ones with the correct
UPDATE
    `stipend_info_10JulDisb_1Jul-1` f
        INNER JOIN
    `stipend_info_10JulDisb_1Jul-1` s ON f.parent_name_unicode = s.parent_name_unicode
        AND f.nationalidoriginal = s.nationalidoriginal
        INNER JOIN
    (SELECT
        COUNT(DISTINCT t.mobile_number) validMobileCount,
            t.mobile_number
    FROM
        `stipend_info_10JulDisb_1Jul-1` t
    WHERE
        ((SUBSTR(t.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
            AND LENGTH(t.`mobile_number`) = 11)
            OR (SUBSTR(t.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
            AND LENGTH(t.`mobile_number`) = 10))
    GROUP BY t.parent_name_unicode , t.nationalidoriginal) t ON f.mobile_number = t.mobile_number
    set s.mobile_number = f.mobile_number
WHERE
    f.mobile_number != s.mobile_number
        AND f.nationalidoriginal != 'Null'
        AND ((SUBSTR(f.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(f.`mobile_number`) = 11)
        OR (SUBSTR(f.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(f.`mobile_number`) = 10))
        AND NOT ((SUBSTR(s.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(s.`mobile_number`) = 11)
        OR (SUBSTR(s.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(s.`mobile_number`) = 10));