When writing emails or online text, it's become common to see UPPER CASE TEXT AS A FORM OF SHOUTING.
Yet I see IT systems every day that still have text that humans are meant to read in all upper-case or all lower-case. Please don't do that to your users.
But how do you fix text like that? We have a tool for that.
The SDU Tools collection has a ProperCase function and a TitleCase function that you can use in T-SQL and stop your SQL Server based applications from shouting at your users.
ProperCase and TitleCase only really differ in how filler words are treated (ie are they capitalized or not). Note the differences shown here:
You can see them in action here:
You can find out more about our free SDU Tools here:
Hi Dr. Greg
I first want to thank you for your SDU Tools code and it saved me a ton of time, and since I am currently working for a BBQ company as a contractor I want to pass along a modification I put in place for the BBQ abbreviation
IF @CurrentWord IN ('PO', 'DC', 'NY', 'BBQ')
BEGIN
SET @ModifiedWord = UPPER(@CurrentWord);
END ELSE BEGIN
SET @ModifiedWord = UPPER(SUBSTRING(@CurrentWord, 1, 1)) + SUBSTRING(@CurrentWord, 2, LEN(@CurrentWord) – 1);
END;
–IF LEFT(@CurrentWord, 2) = N'ga' AND LEN(@CurrentWord) >= 3
— BEGIN
— SET @ModifiedWord = N'GA' + UPPER(SUBSTRING(@CurrentWord, 3, 1)) + SUBSTRING(@CurrentWord, 4, LEN(@CurrentWord) – 3);
— END;
IF LEFT(@CurrentWord, 2) = N'mc' AND LEN(@CurrentWord) >= 3
BEGIN
SET @ModifiedWord = N'Mc' + UPPER(SUBSTRING(@CurrentWord, 3, 1)) + SUBSTRING(@CurrentWord, 4, LEN(@CurrentWord) – 3);
END;
IF LEFT(@CurrentWord, 3) = N'mac' AND LEN(@CurrentWord) >= 4
BEGIN
SET @ModifiedWord = N'Mac' + UPPER(SUBSTRING(@CurrentWord, 4, 1)) + SUBSTRING(@CurrentWord, 5, LEN(@CurrentWord) – 4);
END;
IF LEFT(@CurrentWord, 3) = N'Bbq' AND LEN(@CurrentWord) >= 4
BEGIN
SET @ModifiedWord = N'BBQ' + UPPER(SUBSTRING(@CurrentWord, 4, 1)) + SUBSTRING(@CurrentWord, 5, LEN(@CurrentWord) – 4);
END;
Thank you for your help
Awesome. I like BBQ as a commonly uppercase word. I've added it to v13 coming in the next week or so. BTW, it only needed to be added to the IF @CurrentWord IN line. The others are special cases for common name prefixes.
Thanks for the feedback Andy. Glad the tools are helpful.
Hey Greg, regarding ProperCase, would I be correct in saying that the only way to cater this on an as-needs basis would be to change the code each time? I see you have 'PO', 'DC', 'NY', 'BBQ', 'GPO', but what if I have a one-off requirement, it would be great if I could specify a number of input strings that should remain as is if found that way. eg 'CCU', 'ICU', 'RCH', 'RMH', 'RWH'.
Hi Dale,
Unfortunately yes. With functions in T-SQL, you can't have optional parameters, so adding one would require it being passed every time, and breaking all existing code. I really wish you could build "true" functions in T-SQL, with optional parameters, parameter overloading, etc. etc.