Day: March 29, 2024

---

delete from combined_output_02 where field1 like 'field%'

---

MAKE field9

MAKE URL field

MAKE AreaCode field

MAKE TEL field

---

ALTER TABLE combined_output_02
ADD COLUMN field9 TEXT;

ALTER TABLE combined_output_02
ADD COLUMN URL TEXT;

ALTER TABLE combined_output_02
ADD COLUMN AreaCode TEXT;

ALTER TABLE combined_output_02
ADD COLUMN TEL TEXT;

ID auto number primary key add

MAKE ID field, Primary Key

---

한글 상호, update field7 to field8

UPDATE combined_output_02
SET field8 = field7
WHERE field8 IS NULL;

URL move, field3, field4, field5, field6

UPDATE combined_output_02
SET URL = 
    CASE 
        WHEN field7 LIKE 'URL%' THEN field7
        WHEN field6 LIKE 'URL%' THEN field6
        WHEN field5 LIKE 'URL%' THEN field5
        WHEN field4 LIKE 'URL%' THEN field4
        ELSE URL -- Keep the existing URL value if none of the conditions are met
    END;

do above for field4, field5, field6, etc.

update combined_output_02 set URL = substr(URL, -length(URL) + 5)

add field9, and copy field4, field5, field6 TEL

copy phone TEL: xxx-xxx-xxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL:%' AND LENGTH(field5) = 17 THEN field5
        WHEN field4 LIKE 'TEL:%' AND LENGTH(field4) = 17 THEN field4
        WHEN field6 LIKE 'TEL:%' AND LENGTH(field6) = 17 THEN field6
        ELSE field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

reformat phone TEL: (xxx) xxx-xxxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 19 THEN
            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 12, 3) || '-' || SUBSTR(field5, -4)
        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 19 THEN
            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 12, 3) || '-' || SUBSTR(field4, -4)
        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 19 THEN
            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 12, 3) || '-' || SUBSTR(field6, -4)
        ELSE
            field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

reformat phone TEL: (xxx)xxx-xxxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 18 THEN
            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 11, 3) || '-' || SUBSTR(field5, -4)
        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 18 THEN
            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 11, 3) || '-' || SUBSTR(field4, -4)
        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 18 THEN
            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 11, 3) || '-' || SUBSTR(field6, -4)
        ELSE
            field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

repeat above for TEL: in field4 and field6

area_code field add

UPDATE combined_output_02
SET AreaCode = substr(field9, 6, 3)

UPDATE field9 into TEL

update combined_output_02 set TEL = substr(field9, -12) where field9 is not null

AreaCode_TimeZone import

StandardTimeZone add

select combined_output_02.* , AreaCode_TimeZone.StandardTimeZone from combined_output_02 LEFT join AreaCode_TimeZone on combined_output_02.AreaCode = AreaCode_TimeZone.AreaCode

unique_phone sort out

SELECT MIN(ID) AS min_ID, TEL
FROM combined_output_02_timezone
GROUP BY TEL

select * from combined_output_02_timezone where ID in (select min_ID from unique_phone)

---