The nice thing about side-zip pants...

is that I never forget to zip them up. Since I started working here a few months ago, I've expanded my "work pants" collection by a couple of pairs of pants. 80-85% of them (or at least the ones I wear 80-85% of the time) have a button on the inside of the waistband, along with one or two on the outside followed by a zipper in the front. This leads to embarrassment about 50% of the time I wear them. In other words, I forget to zip them up. I had this problem at my last job MAYBE once a year. For those of you math wizards out there, my current rate is more like 2 times per week (4.25 times in a 10 day period).

It's not that I don't know that there's a zipper there. But by the time I button and button and button and sometimes belt, it's hard to remember the zipper. Especially since I want to spend as little time as possible in the restroom. So there I go, back to my desk, sometimes all the way home, and it's not until I "go" at home do I realize that I likely embarrassed myself for 1/2 a day at work and on the train ride home. Retro-grade embarrassment is almost worse than actual embarrassment because when it's reflective like that, you imagine all the worst things rather than the small (or huge) amount of embarrassment that occurs when you KNOW something embarrassing is happening.

Or maybe I should be thinking more positive thoughts. Rather than being mortified all alone when I figure out what I've done, I should be proud that I got away with indecent exposure all day without a fine.

Sunshiny day

It's beautiful outside. Not a cloud in the sky, birds singing, the whole nine yards. It's like a Disney movie out there. I couldn't help myself, so I took at walk at lunch. I'm wearing a 3/4 sleeved shirt, but I went without a jacket - it's at least 51 degrees out there.  In JANUARY.

As I walked happily around in the warmth and sunshine, I noticed my fellow walkers were all wearing coats and scarves.  Apparently, 50s is still cold for Portlanders. I'm not really looking forward to going back to Omaha weather in February.

I did it again!

 And I'm going to bore you to death again (sorry about that). I finally figured out how to take LastName, FirstName MiddleNmame and turn it into 3 different columns:  LastName   FirstName   MiddleName.  I know it looks exactly the same as what I did 2 posts ago, but that one did not have a space after the comma, and this one does.  No big thing, except it is.  And I'm going to share the code.  Because it's probably even better than the last one.

SELECT CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN TABLE.FIELD --if there's no comma, it's a last name 
WHEN LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD)+1),LEN(TABLE.FIELD))) > LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+1),LEN(TABLE.FIELD))) 
THEN SUBSTRING(TABLE.FIELD,1,CHARINDEX(',',TABLE.FIELD)-1) 
ELSE SUBSTRING (TABLE.FIELD,1,CHARINDEX(',',TABLE.FIELD)-1)
END AS LAST_NAME 
,CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN ' ' --IF THERE'S NO COMMA, IT'S A LAST NAME 
WHEN LEN(SUBSTRING(TABLE.FIELD,CHARINDEX(' ',TABLE.FIELD),LEN(TABLE.FIELD)))-(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX
(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD)))) > 0 
THEN SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2),(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+1),LEN(TABLE.FIELD)))-(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD)))))) 
ELSE SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2),(LEN(TABLE.FIELD)))
END AS FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN ' ' --IF THERE'S NO COMMA, IT'S A LAST NAME
WHEN (CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))) > 0 
THEN SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD))
 ELSE ' ' 
END AS MIDDLE_NAME 
FROM TABLE

chop chop

I need a haircut.  There was a time in my life when I would get my hair trimmed every 6 weeks.  I'd want to do it after 4, but I'd force myself to wait it out 2 more weeks.  Now?  It's been a while.  Like....12 weeks or more.  Back during the "every 6 weeks" time of my life, I had short hair.  I had to keep it in check, lest I begin to look like Justin Beiber.  Back then, people didn't purposely want to look like that.  It was also before the recession.  And by "the recession," I mean Bubba. 

Having a child is expensive.  Having two in daycare is like paying for a new car every six months.  Coincidentally (or not), I started growing my hair out.  That pushed the trims out to once every 8 weeks.  Even then, it was hard to justify the expense - both time and money.  Before, I was working around my schedule and Scooter's schedule to make hair appointments.  It suddenly became my schedule, Scooter's schedule and Ben's schedule (if Ben wasn't available, who would watch Bubba?!). 

Plus, no matter the genius of my hair dresser (and she was awesome!), I've found that no amount of "wash and go!" styles can make a new mother look good.  At least not for a year or so.  There's just no "me" time involved in new-motherhood, and I was one of the lucky new moms who lost all her hair - in clumps.  So there you are, spending hard-earned money on a hair style that won't really make you feel human.  But it's really as close as I could get, so I kept at it.  After we went down to one child in daycare again, the $ expense was a little easier to justify (for me, at least).

And then something else happened to derail my regular hair appointmets.  I moved.  It's hard finding a hair person you can trust.  It's even harder when your hair is like mine:  Asian-ish (so...thick strands) but frizzy with some curls (nape of my neck and around my hairline plus random waves in spots throughout) and all around unruly - not really "asian" enough to go to someone who knows what they're doing with Asian hair, but not "white" enough to stick to the strictly caucasian specialists. It usually takes a professional a few haircuts to really get a hang of what's going on up there.  Erin (my Omaha hair dresser) was fantastic.  She "got" it after the first cut.  How do you go from that to the unknown world of Oregon hair dressers? 

I thought when I first moved here that I'd find lots of great places.  Bigger city, west coast chic, etc.  But really, it's overwhelming.  I don't want to go to the most expensive place (see my tirad above about expenses), but in my adult life, I've never had a good experience at one of those cost cutter type places.  So I'm shopping around.  Slowly.  As in...I'm not really getting my hair trimmed, anymore.  Ben and I joke that I have quickly become a granola crunching, plaid wearing "Portlander," so I guess it would follow that I'd quit cutting my hair (along with shaving my legs or underarms) and really embrace this mess I'm in.

 

Blueberries

Ben bought a giant tub of blueberries last week.  They were on sale, and they are delicious.  We're not quite done with them, but we're getting there.  Blueberries are Bubba and my favorite berry.  In fact, he asked for them by name today.  On the surface, that's no big deal.  He asks for things all the time, and he knows all the words for foods.  But after he asked for "blueberries.  peace," Ben wondered aloud if it was his first three syllable word.  And that is impressive!  Of course, as I type this, I realize that it wasn't.  He said, "banana" quite clearly as his 3rd or 4th word well over a year ago.  It pretty much went:  da-da, ee-yee (his sister's name), banana and THEN ma-ma.  Got to have your priorities, and he used to LOOOOVE bananas.

bus!

Bubba loves buses.  It sounds like "buthsssss"  (emphasis on the 's') when he talks about them.  And he talks about them everyday.  "Mama choo-choo.  Daddy choo-choo.  ee-yee buthssssss.  ee-oh buthssss."  And then he talks about the bus at school, the bus he saw on the street, etc.  You get the picture.  Today, I rode a buthsssss to work.  I took my usual train, but it was raining pretty hard, and I had left the umbrella at home (I didn't want to carry it on the plane, and I thought Ben and Bubba could use it this weekend).  I also had my laptop in my backpack (which does do a good job of keeping water out in general, but it's not waterproof or anything).  So I got off my train at my normal stop, and ran a block to catch the bus.  He was already there when I got off the train, but he saw me running.  I felt good to have made it.  The guy behind me wasn't so lucky.  The driver took off just as he was knocking on the door. 

I think all of the commute operators were crabby today.  The train operator got on the intercom at Sunset (which is the busiest stop on my route), after we had been there for less than a minute and said, "Ladies and gentlemen:  your commute is being delayed because someone is blocking the door."  It was pretty abrupt and I found it kind of rude, but it got the job done.  As soon as he made the announcement, the doors closed and off we went.  I had been warned by coworkers when I first moved here that by January or February, people get really grouchy around Portland.  Too much rain is the theory, but it's probably not enough sun.  Which might sound like the same thing, but it's not.

What I found from my train-bus commute into work is that It doesn't really cut any time out of my commute.  Walking 14 or 15 blocks takes me about the same time as it does for a bus to drive it while stopping every few blocks.  I also discovered that there's a lot of walking involved in riding a bus.  Like...6 blocks of walking.  An alternate route that would take more time but require less walking is taking the train another stop or two further than normal and catching the trolley which stops right in front of the parking lot of my building.  I just don't think that's really necessary.

Overall, I like the walk into work.  This week, the birds are back, and they sing to me on my way in.  If I wasn't walking through a downtown residential area wearing rainboots, I'd almost feel like a Disney princess in the mornings.  Also, and this is key.  Walking to work affords me the opportunity to cash in on Portland's generosity.  I found a nickel yesterday.  So far this week, that's 10 cents.  If you add in Ben's "earnings," we're up to $2.43 found on the streets in Oregon (except for 1 penny in Omaha's airport and 2 pennies in Seattle, WA).  Not only does taking the train and walking save me at least $80 a month in gas, but it pays me, too!

If I haven't already, I'm about to bore your socks off.

CAUTION:  Work post.  Don't worry.  I won't regale you with stories about the moldy food in the break room (although I found some on Monday), nor will I whine about my coworkers (mostly because so far, they're all pretty cool and WAAAY more qualified than I).  What I'm about to do is much worse than that.  I'm going to tell you about what I did today.  For seven hours.  Hopefully, it won't take you seven hours to read through this.  If so, I apologize (and suggest that you get a tutor).

What I was trying to do is turn "Lastname,Firstname Middlename" into "Lastname"  "Firstname"  "Middlename."  I know.  It's not very exciting at all.  But I was trying to do it anyway, and my google searches were yielding little by way of instruction.  What I did find was this site which gave me this code:

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME FROM
  (
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (  
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME    
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME 

The problem with that code is that it puts everything into fakey jake areas and calls on them later, blah blah blah long story short, I'm too stupid to figure out how to take that code and put it into my own select statement and get other fields that I want.  So....it only took me seven hours, but I did it.  Here it is.  So pretty.  So nice...

/*WHEN NAMES ARE FORMATTED: "LAST,FIRST MIDDLE" (NO SPACE BEFORE OR AFTER THE COMMA)
OR AS COMPANY NAMES, IE: "21ST CENTURY INSURANCE" THIS CODE WILL RETURN 3 COLUMNS
"FIRST_NAME" "MIDDLE_NAME" "LAST_NAME" WITH FIELDS POPULATED CORRECTLY. IN THE INSTANCE
WHERE THE NAME IS A COMPANY NAME (AS LONG AS THERE ARE NO COMMAS IN THE NAME) IT WILL RETURN
THE COMPANY NAME IN THE "LAST_NAME" FIELD.
THIS IS WAY CONVOLUTED, SO IF THERE'S A BETTER WAY, PLEASE LET ME KNOW. BUT WITH THIS
TECHNIQUE, YOU CAN SLIDE IT INTO YOUR NORMAL SELECT STATEMENT W/O HAVING TO CREATE TEMP TABLES, ETC.
OBVIOUSLY, YOU'LL HAVE TO CHANGE YOUR TABLE AND COLUMN NAMES TO FIT WHAT TABLES YOU'RE USING*/SELECT
YOUR_FIELD
,CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)
THEN NULL --no comma? it's the last name WHEN 0 >= (LEN(substring(YOURTABLE.YOUR_FIELD,CHARINDEX(' ',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))) -
LEN(SUBSTRING(YOURTABLE.YOUR_FIELD,CHARINDEX(',',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))))*-1
THEN SUBSTRING(YOURTABLE.YOUR_FIELD,
(CHARINDEX(',',YOURTABLE.YOUR_FIELD)+1),LEN(YOURTABLE.YOUR_FIELD))
--need to pull in only the first name, not the first name w/ the middle initial at the end
--if there's no space after the first name, sql gets confused as to what is the first name ELSE SUBSTRING(YOURTABLE.YOUR_FIELD
,CHARINDEX (',',YOURTABLE.YOUR_FIELD,+1)+1
,(LEN(substring(YOURTABLE.YOUR_FIELD,CHARINDEX(' ',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))) -
LEN(SUBSTRING(YOURTABLE.YOUR_FIELD,CHARINDEX(',',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))))*-1
)
END AS FIRST_NAME
,CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)
THEN NULL --no comma? it's the last name WHEN 0 = CHARINDEX(' ',YOURTABLE.YOUR_FIELD)THEN NULL --no spaces? assume it's a last nameWHEN 1 <= CHARINDEX(' ',SUBSTRING( YOURTABLE.YOUR_FIELD
,1
,CHARINDEX(',',YOURTABLE.YOUR_FIELD)-1
))THEN NULL -- there is a space before the comma? assume the space is in the last nameELSE
SUBSTRING(YOURTABLE.YOUR_FIELD
,1 + CHARINDEX(' ',YOURTABLE.YOUR_FIELD)
,LEN(YOURTABLE.YOUR_FIELD)
)
END AS MIDDLE_NAME
, CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)THEN YOURTABLE.YOUR_FIELD --if there's no comma, it's a last nameELSE SUBSTRING (YOURTABLE.YOUR_FIELD
,1
,CHARINDEX(',',YOURTABLE.YOUR_FIELD)-1
)END AS LAST_NAME

FROM YOURTABLE

Actually, it's a whole lot of crazy, and I'm sure there's a better way to do this.  But in case you're searching for it, here's an easy way to grab portions of text from a field in SQL.  Hooray for determination!