Capitalizing Text in SQL Server
I recently had to scramble to put together a script to change a column of strings from being all UPPERCASE to being Capitalized. It wasn’t enough to just capitalize the first letter of the string because some of the strings were multiple words that all needed to be capitalized.
Luckily I stumbled upon this function at http://www.sql-server-helper.com/functions/initcap.aspx which is an implementation of a built in function for Oracle, but is missing in MSSQL
CREATE FUNCTION [dbo].[InitCap] ( @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) END IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(') BEGIN IF @PrevChar != '''' OR UPPER(@Char) != 'S' SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char)) END SET @Index = @Index + 1 END RETURN @OutputString END GO
I realized that I could leverage this function to aid in updating my dataset to set the strings to their proper casing. In order to accomplish this, I had to write another query which would let me update each individual row in the table with a different value.
The method I chose involved an in-memory table to store the capitalized names which I could then join to my
UPDATE query to complete the process
DECLARE @Names TABLE (id INT, name NVARCHAR(100)) INSERT INTO @Names (id, name) (SELECT [id], dbo.InitCap([name]) as name FROM Counties) UPDATE Counties SET name = (SELECT name FROM @Names as n WHERE n.id = Counties.id)
The temporary table has two columns,
name. I insert the
id from the base table, as well as the modified
name which has the
InitCap function applied to it.
Once the table is created, it’s attached to the
UPDATE statement to select the correct data from the temporary table and store it to the linked column in the base table.