SQL for LCP

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,

Chittenden
Chittenden Co
Chittenden Co.

Would be nice for them all to be Chittenden

Can you advise how to do that?

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

In this case, the SQL command of interest is the UPDATE command (see here). In your particular case, the command would like like this

    update VTLANDSCAPEDB set COUNTY="Chittenden" where COUNTY like "Chittenden%"

the

    like "Chittenden%"

clause contains a "wild card" character — % — that ensures that any record (and ONLY those records) whose COUNTY field begins with Chittenden will be affected.

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,

    UPDATE VTLANDSCAPEDB set COUNTY=REPLACE(COUNTY," Co.","")  where COUNTY regexp "(.*) Co.$"

This takes care of the " Co." entries. Repeat with a slight modifiction

    UPDATE VTLANDSCAPEDB set COUNTY=REPLACE(COUNTY," Co","")  where COUNTY regexp "(.*) Co$"

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.

About Wesley Wright

Born on a mountain top near New York City, Craziest state in the land of the pretty. Raised in the woods so's he knew every tree, Killed him a bear when he was only three.
This entry was posted in Landscape Change, Projects. Bookmark the permalink.

Leave a Reply