Capitalize a string

When you look at your data, do you see a mish mash of formats?  Do some people use camel case, some all lowercase, some upper?  Here is a quick script that you can use to capitalize the first letter in your single word strings.

Below you can see in my traffic_sources table, some of the source_names are camel case and lower case.  What I want, is to have them all to have the first character capitalized.

 

When I work on problems like this, I like to break it up into steps to make sure it does what I am expecting it to do.  First I want to get the first character in an upper case format.  I am going to use the built in SUBSTRING function for this.  The basic usage for this function is: substring(expression, start, length).  See this link for more information:  Books Online Substring .  We are passing the fieldname and 1 for the starting position and another 1 for length since we only want the first character.

Below, you can see I am also using an UPPER function.  This function returns whatever string you pass to it, to uppercase.  In the query results, the column named First_Letter shows the results of this function.

 

 

We now need to get the rest of the string in a lower case format.  We will use the substring function again, but this time we are going to skip to the second character for a start position.  For the length, we will use 10 since our longest string is under 10 characters.  You will see I am also using the LOWER function to make this string lower case as shown in the Rest_Of_String column.

 

Now we need to put them together to get a complete string in the format we want.  The yellow line below is just taking the First_Letter column and concatenating it with the Rest_of_String column using the + Plus sign into the All_Together column seen below.  This column shows the string with an upper case first letter, and the rest lowercase.

 

Now we need to update the data.  We will use a simple update statement, and we will set the source_name equal to the All_Together column in the above query for all rows in the table.  If you are using SSMSBoost like I am, you get a warning when you run an update or delete statement without a WHERE clause which could be a life saver if you really do not want all rows to be updated.  I highly recommend this SSMS plugin, you can read my blog post on other great things it does here..

 

 

We press Yes, to run the query, and it updates 3 rows.

We query the data again, and we can see it now is in a format that we prefer, with the first character capitalized.

This query will work for multi word strings if you want all of the other words lower case.  If you want each word with a first letter capital letter, you will need to use other functions like CHARINDEX and LEN and may need to write a custom function to do this.  This may be a future blog post if time allows.

Thanks for reading.