The CASE function in SQL is a great way to customize the output of your SQL to better suit the needs of your End Users. She has a great start, but we are going to get her up to the next level, and one way is going to be the usage of CASE in her new SQL. In fact, it will loop just once for each word that needs to be capitalized.Am currently working with an individual, hoping to help her improve her SQL skills.
It will correctly capitalize all words and it minimizes the number of loops. Return my opinion, there are several things that make this function better. Select = 2, = PatIndex('%%', COLLATE Latin1_General_Bin) Select = 1, 1, = PatIndex('%%', COLLATE Latin1_General_Bin) Notice how the 7th character (the lower case c) is replaced with an upper case C. Stuff is different in that you can replace data based on its position within a string without regard to what is at that position. With replace, you essentially replace all occurrences of one string with another. It can also be used similar to a Replace function. Stuff can be used to insert text in to the middle of a string. PatIndex(‘%%‘, COLLATE Latin1_General_Bin) You can get case sensitive searches by using a binary collation. This is actually controlled by the collation of your database. By default, searches are not case sensitive. In our case, we want to find a “non-alpha character followed by a lower case alpha character”. PatIndex searches through a string and returns the position where a search pattern is found. The less common functions here are PatIndex and Stuff. SubString allows you to retrieve data from the middle of string.
This code uses several functions, some that you may not be familiar with. We then repeat this process until there are no more occurrences of non-alpha followed by lower case alpha characters. We then use the stuff function to convert this to upper case. In this case, we would return character position 5 (space followed by lower case i). Next, we actively seek out positions where one character is non-alphabetic followed by a lower case alphabetic character. For example, suppose you have this: THIS IS-ALL “Caps”Īt the beginning of the function, we convert to lower case except for the first character, so it becomes: The first thing we do is convert the string to lower case except for the first character, which is converted to upper case. Like I stated earlier, this code actively seeks out data from within the string that needs to be changed. This method also reduces the number of loops that need to be executed by using the PatIndex function and looking for non-alpha characters followed by lower case alpha characters. Under certain circumstances, this is more appropriate because it satisfies more situations. I have developed a method that actively seeks out any character that is not a letter. Unfortunately, this is not always adequate. The simplest versions of this will loop through a string, character by character, and capitalize anything following a space. Some work better than others, and perform better than others. There are many examples on the internet that show how this can be done. This functionality is useful in many different situations. With SQL, it is easy to convert strings to upper case or lower case, but what about making it mixed case/title case.
In situations like this, it is acceptable to write and use a function like this. That being said, it’s not uncommon to have data in your tables that needs to be “cleaned up a bit”. String functions are generally slow and often a bit cumbersome to implement. SQL Server (T-SQL specifically) is not usually the best place to write a function for modifying the case of your data.