sqlsql-serverstring

How to capitalize the first letter of each word in a string in SQL Server


How do I capitalize the first letter of each word in a string in SQL Server?


Solution

  • From http://www.sql-server-helper.com/functions/initcap.aspx

    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
    

    There is a simpler/smaller one here (but doesn't work if any row doesn't have spaces, "Invalid length parameter passed to the RIGHT function."):

    http://www.devx.com/tips/Tip/17608