{"id":1264,"date":"2024-03-19T11:33:26","date_gmt":"2024-03-19T15:33:26","guid":{"rendered":"https:\/\/folvera.commons.gc.cuny.edu\/?p=1264"},"modified":"2024-03-19T11:36:47","modified_gmt":"2024-03-19T15:36:47","slug":"introduction-to-structured-query-language-for-data-analytics-ws24sql10001-day-3","status":"publish","type":"post","link":"https:\/\/folvera.commons.gc.cuny.edu\/?p=1264","title":{"rendered":"Introduction to Structured Query Language for Data Analytics (WS24SQL10001) \u2014 Day 3"},"content":{"rendered":"<p>We have started working with functions to manipulate strings.<\/p>\n<pre><span style=\"color: #0000ff\">    SELECT<\/span> phone <span style=\"color: #0000ff\">AS<\/span> original_phone,  <span style=\"color: #008000\">-- from `8005551205` to `(800) 555-1205`<\/span>\r\n      <span style=\"color: #0000ff\">LEFT<\/span>(phone, 3) <span style=\"color: #0000ff\">AS<\/span> area_code,  <span style=\"color: #008000\">-- extracting `800`<\/span>\r\n      <span style=\"color: #0000ff\">SUBSTRING<\/span>(phone, 4, 3) <span style=\"color: #0000ff\">AS<\/span> branch_exchange,  <span style=\"color: #008000\">-- extracting `555`<\/span>\r\n      <span style=\"color: #0000ff\">RIGHT<\/span>(phone, 4) <span style=\"color: #0000ff\">AS<\/span> subscriber_number,  <span style=\"color: #008000\">-- extracting `1205`<\/span>\r\n      <span style=\"color: #0000ff\">CONCAT<\/span> (  <span style=\"color: #008000\">-- concatenating values 1 to 6<\/span>\r\n        `(`,  <span style=\"color: #008000\">-- value 1, hard-coded the opening parenthesis<\/span>\r\n          <span style=\"color: #0000ff\">LEFT<\/span>(phone, 3),  <span style=\"color: #008000\">-- value 2, area code from `phone`<\/span>\r\n          `) `,  <span style=\"color: #008000\">-- value 3, hard-coded closing parenthesis + space<\/span>\r\n        <span style=\"color: #0000ff\">SUBSTRING<\/span>(phone, 4, 3),  <span style=\"color: #008000\">-- value 4, branch from `phone`<\/span>\r\n        ` - `,  <span style=\"color: #008000\">-- value 5, hard-coded hyphen<\/span>\r\n        <span style=\"color: #0000ff\">RIGHT<\/span>(phone, 4)  <span style=\"color: #008000\">-- value 6, subscriber number from `phone`<\/span>\r\n        ) <span style=\"color: #0000ff\">AS<\/span> legible_phone\r\n    FROM table1;\r\n<\/pre>\n<p>Download the <a href=\"https:\/\/folvera.commons.gc.cuny.edu\/wp-content\/blogs.dir\/2501\/files\/2024\/03\/WS24SQL10001_20240318.pdf\">class notes<\/a> for day 3.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We have started working with functions to manipulate strings. SELECT phone AS original_phone, &#8212; from `8005551205` to `(800) 555-1205` LEFT(phone, 3) AS area_code, &#8212; extracting `800` SUBSTRING(phone, 4, 3) AS branch_exchange, &#8212; extracting `555` RIGHT(phone, 4) AS subscriber_number, &#8212; extracting `1205` CONCAT ( &#8212; concatenating values 1 to 6 `(`, &#8212; value 1, hard-coded the [&hellip;]<\/p>\n","protected":false},"author":7213,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"send_to_group_blog":0,"footnotes":""},"categories":[34,3,1,2],"tags":[],"class_list":["post-1264","post","type-post","status-publish","format-standard","hentry","category-ws24sql10001","category-computer-programming","category-cuny","category-sql"],"_links":{"self":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1264","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/users\/7213"}],"replies":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1264"}],"version-history":[{"count":8,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1264\/revisions"}],"predecessor-version":[{"id":1273,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1264\/revisions\/1273"}],"wp:attachment":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}