{"id":3731,"date":"2024-03-29T14:11:50","date_gmt":"2024-03-29T22:11:50","guid":{"rendered":"https:\/\/crm270s.abramjmeister.com\/?p=3731"},"modified":"2024-03-29T14:11:50","modified_gmt":"2024-03-29T22:11:50","slug":"%ec%a4%91%ec%95%99%ec%9d%bc%eb%b3%b4-yellowpage-after-combined_output_02","status":"publish","type":"post","link":"https:\/\/crm270s.mycpamytax.com\/?p=3731","title":{"rendered":"\uc911\uc559\uc77c\ubcf4 Yellowpage after combined_output_02"},"content":{"rendered":"\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-wide\"\/>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>delete from combined_output_02 where field1 like 'field%'\n<\/strong><\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-wide\"\/>\n\n\n\n<p class=\"has-cyan-bluish-gray-color has-text-color has-link-color wp-elements-1 wp-block-paragraph\">MAKE field9<\/p>\n\n\n\n<p class=\"has-cyan-bluish-gray-color has-text-color has-link-color wp-elements-2 wp-block-paragraph\">MAKE URL field<\/p>\n\n\n\n<p class=\"has-cyan-bluish-gray-color has-text-color has-link-color wp-elements-3 wp-block-paragraph\">MAKE AreaCode field<\/p>\n\n\n\n<p class=\"has-cyan-bluish-gray-color has-text-color has-link-color wp-elements-4 wp-block-paragraph\">MAKE TEL field<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-dots\"\/>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE combined_output_02\nADD COLUMN field9 TEXT;\n\nALTER TABLE combined_output_02\nADD COLUMN URL TEXT;\n\nALTER TABLE combined_output_02\nADD COLUMN AreaCode TEXT;\n\nALTER TABLE combined_output_02\nADD COLUMN TEL TEXT;<\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-5 wp-block-paragraph\"><strong>ID auto number primary key add<\/strong><\/p>\n\n\n\n<p class=\"has-black-color has-text-color has-link-color wp-elements-6 wp-block-paragraph\">MAKE ID field, Primary Key<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-wide\"\/>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-7 wp-block-paragraph\"><strong>\ud55c\uae00 \uc0c1\ud638, update field7 to field8<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE combined_output_02\nSET field8 = field7\nWHERE field8 IS NULL;<\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-8 wp-block-paragraph\"><strong>URL move, field3, field4, field5, field6<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE combined_output_02\nSET URL = \n    CASE \n        WHEN field7 LIKE 'URL%' THEN field7\n        WHEN field6 LIKE 'URL%' THEN field6\n        WHEN field5 LIKE 'URL%' THEN field5\n        WHEN field4 LIKE 'URL%' THEN field4\n        ELSE URL -- Keep the existing URL value if none of the conditions are met\n    END;<\/code><\/pre>\n\n\n\n<p class=\"has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-9 wp-block-paragraph\"><strong>do above for field4, field5, field6, etc.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>update combined_output_02 set URL = substr(URL, -length(URL) + 5)<\/strong><\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-10 wp-block-paragraph\"><strong>add field9, and copy field4, field5, field6 TEL <\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>copy phone TEL: xxx-xxx-xxx:\n<\/strong>\nUPDATE combined_output_02\nSET field9 = \n    CASE \n        WHEN field5 LIKE 'TEL:%' AND LENGTH(field5) = 17 THEN field5\n        WHEN field4 LIKE 'TEL:%' AND LENGTH(field4) = 17 THEN field4\n        WHEN field6 LIKE 'TEL:%' AND LENGTH(field6) = 17 THEN field6\n        ELSE field9 -- Keep the existing value of field9 if none of the conditions are met\n    END;\n\n<strong>reformat phone TEL: (xxx) xxx-xxxx:\n<\/strong>\nUPDATE combined_output_02\nSET field9 = \n    CASE \n        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 19 THEN\n            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 12, 3) || '-' || SUBSTR(field5, -4)\n        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 19 THEN\n            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 12, 3) || '-' || SUBSTR(field4, -4)\n        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 19 THEN\n            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 12, 3) || '-' || SUBSTR(field6, -4)\n        ELSE\n            field9 -- Keep the existing value of field9 if none of the conditions are met\n    END;\n\n<strong>reformat phone TEL: (xxx)xxx-xxxx:\n<\/strong>\nUPDATE combined_output_02\nSET field9 = \n    CASE \n        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 18 THEN\n            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 11, 3) || '-' || SUBSTR(field5, -4)\n        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 18 THEN\n            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 11, 3) || '-' || SUBSTR(field4, -4)\n        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 18 THEN\n            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 11, 3) || '-' || SUBSTR(field6, -4)\n        ELSE\n            field9 -- Keep the existing value of field9 if none of the conditions are met\n    END;<\/code><\/pre>\n\n\n\n<p class=\"has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-11 wp-block-paragraph\"><strong>repeat above for TEL: in field4 and field6<\/strong><\/p>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-12 wp-block-paragraph\"><strong>area_code field add<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE combined_output_02\nSET AreaCode = substr(field9, 6, 3)<\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-13 wp-block-paragraph\"><strong>UPDATE field9 into TEL<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>update combined_output_02 set TEL = substr(field9, -12) where field9 is not null<\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-14 wp-block-paragraph\"><strong>AreaCode_TimeZone import<\/strong><\/p>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-15 wp-block-paragraph\"><strong>StandardTimeZone add<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select combined_output_02.* , AreaCode_TimeZone.StandardTimeZone from combined_output_02 LEFT join AreaCode_TimeZone on combined_output_02.AreaCode = AreaCode_TimeZone.AreaCode<\/code><\/pre>\n\n\n\n<p class=\"has-white-color has-cyan-bluish-gray-background-color has-text-color has-background has-link-color wp-elements-16 wp-block-paragraph\"><strong>unique_phone sort out<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT MIN(ID) AS min_ID, TEL\nFROM combined_output_02_timezone\nGROUP BY TEL<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from combined_output_02_timezone where ID in (select min_ID from unique_phone)\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-wide\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"> <\/p>\n","protected":false},"excerpt":{"rendered":"<p>MAKE field9 MAKE URL field MAKE AreaCode field MAKE TEL field ID auto number primary key add MAKE ID field, Primary Key \ud55c\uae00 \uc0c1\ud638, update field7 to field8 URL move, field3, field4, field5, field6 do above for field4, field5, field6, etc. add field9, and copy field4, field5, field6 TEL repeat above for TEL: in field4 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/crm270s.mycpamytax.com\/?p=3731\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;\uc911\uc559\uc77c\ubcf4 Yellowpage after combined_output_02&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[42],"tags":[],"class_list":["post-3731","post","type-post","status-publish","format-standard","hentry","category--yellowpage"],"_links":{"self":[{"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=\/wp\/v2\/posts\/3731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3731"}],"version-history":[{"count":0,"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=\/wp\/v2\/posts\/3731\/revisions"}],"wp:attachment":[{"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/crm270s.mycpamytax.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}