Script for using mixed capitalization of Caption field

mr.e

A few weeks ago, I posted a question, asking for how to convert capitalization to either all lowercase or all upppercase.  I received a great script from Lawrence Garvin, which I then shared with my team. They like it, but they would like to know if there is a way to change capitalization for only part of the Caption.  Let me explain...

We have many offices in the USA and other countries.  So, we tend to use the following naming convention...

1.First two letters -- Identify the country (i.e. US, AR, CH, etc.)
2.Next three letters -- Identify the city (i.e. SOD, BUR, JAX, etc.)
3.Next two to seven letters -- Identify the device type (i.e. ,RT, SWCOR, VIDCAM, etc.)
4.Last set of 2 to 6 numbers -- To be used as per discretion by the respective local team

So, for example, a device could be named something like usstuswcor0102 for a core switch hosted in a fictional office at Sturgis (South Dakota), in the US -- of course. So, what our team wants is to conver to lowercase items 1 and 3 above and to convert to uppercase item #2, identifying the city). So, this seems like two or three different scripts.  I was actually thinking of first changing all letters to lowercase (using Lawrence Garvin's script).  Then, I'd run another script (TBD) to change capitalization to uppercase for the letters in positiion 3 - 5 for each of the caption fields. 

Once the script is run, the caption for the node would change from usstuswcor0102 to usSTUswcor0102 -- making the city stand out.

Any ideas???

LGarvin

Short answer today...

So, what our team wants is to conver to lowercase items 1 and 3 above and to convert to uppercase item #2, identifying the city).

This can actually be done with a single SQL UPDATE statement with creative use of the SUBSTRING() function along with LOWER() and UPPER(), completely independent of what the current case structure may be. Since we know the position of the characters, we don't even have to do string matching, only positional matching -- but if you wanted to do string validation as well, that could be done.

I'll get back to you in the next day with the exact syntax for this conversion.