When printing off address lables I noticed the title field was all different cases: MR mr Mr and mR abounded.
This doesnt look very professional on letters!
Microsoft provides Ucase to make a text field upper case and Lcase to make it lower case. Its easy to combine the two to make a field have capital first letters and lower case for the remainder:
Ucase(left({Field},1)) & Lcase(right({Field},len({Field})-1))
Often there is a requirement to Capitalize the inital Letter of Each Word in a String. The following function does it quickly and easily.
CREATE FUNCTION [dbo].[FN_INITIALCAPS] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000) AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ‘ ‘
ELSE SUBSTRING(@InputString, @Index – 1, 1)
ENDIF @PrevChar IN (‘ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-’, ‘/’, ‘&’, ””, ‘(‘)
BEGINIF @PrevChar != ”” OR UPPER(@Char) != ‘S’
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
ENDSET @Index = @Index + 1
ENDRETURN @OutputString
END
It is sometimes necessary to split a string before or after a specified character. For instance if you are trying to strip the username out of a name/domain field you can use the following code.
SELECT SUBSTRING(SYSTEM_USER, CHARINDEX(‘\’, SYSTEM_USER) + 1, LEN(SYSTEM_USER))
It Will return username given domain\username.
The code can then be extended to work around any character specified. For instance if you have a name field which is [firstname] [lastname] you can split it into seperate firstname and lastname fields.
SUBSTRING([Field], 1, CHARINDEX(‘ ‘, [Field]) – 1)SUBSTRING([Field], CHARINDEX(‘ ‘, [Field]) + 1, LEN([Field]))