{"id":311,"date":"2005-10-25T15:24:39","date_gmt":"2005-10-25T19:24:39","guid":{"rendered":"http:\/\/www.uvm.edu\/~waw\/blog\/?p=311"},"modified":"2005-10-25T15:24:39","modified_gmt":"2005-10-25T19:24:39","slug":"sql-for-lcp","status":"publish","type":"post","link":"https:\/\/blog.uvm.edu\/waw\/2005\/10\/25\/sql-for-lcp\/","title":{"rendered":"SQL for LCP"},"content":{"rendered":"

Paul bierman wanted to know<\/p>\n

There is a database flow left from the Dave days that you and I spoke about on the green last week.<\/span>

That is, for most every county there are three versions of the county name.<\/span>

for example,<\/span>

Chittenden<\/span>
Chittenden Co<\/span>
Chittenden Co.<\/span>

Would be nice for them all to be Chittenden<\/span>

Can you advise how to do that?<\/span><\/div>\n

I suggested it may be of benefit to spend some time studying the science of databases. Here’s an SQL primer,<\/a> google will point you to many more<\/p>\n

In this case, the SQL command of interest is the UPDATE command (see here<\/a>). In your particular case, the command would like like this<\/p>\n

    update VTLANDSCAPEDB set COUNTY="Chittenden" where COUNTY like "Chittenden%"<\/pre>\n
the<\/p>\n
    like "Chittenden%"<\/pre>\n
clause contains a "wild card" character — % — that ensures that any record (and ONLY those records) whose COUNTY field begins with Chittenden will be affected.<\/p>\n
That’s well and good — but needs to be repeated for every county. Better still is a command that would change them all at once. For this, we can use a "regular expression" to match any COUNTY field ending in "Co.", and replace the " Co." part of the field with the empty string "". Hence,<\/p>\n
    UPDATE VTLANDSCAPEDB set COUNTY=REPLACE(COUNTY," Co.","")  where COUNTY regexp "(.*) Co.$"<\/pre>\n
This takes care of the " Co." entries. Repeat with a slight modifiction<\/p>\n
<\/p>\n
    UPDATE VTLANDSCAPEDB set COUNTY=REPLACE(COUNTY," Co","")  where COUNTY regexp "(.*) Co$"<\/pre>\n
A discussion of Regular Expressions is beyond the scope of this note. I tried this on my test database, and it seemed to work just fine.<\/p>\n","protected":false},"excerpt":{"rendered":"
Paul bierman wanted to know There is a database flow left from the Dave days that you and I spoke about on the green last week.That is, for most every county there are three versions of the county name.for example,ChittendenChittenden … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38506,6517],"tags":[],"class_list":["post-311","post","type-post","status-publish","format-standard","hentry","category-landscape-change","category-projects"],"_links":{"self":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts\/311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/comments?post=311"}],"version-history":[{"count":0,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts\/311\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/media?parent=311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/categories?post=311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/tags?post=311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}