P0003 – InitCap / String Capitalization Function

BIDA Forums Forums Technical Topics 2-Databases 2-SQL Server P0003 – InitCap / String Capitalization Function

Viewing 0 reply threads
  • Author
    Posts
    • #272
      Peter Jones
      Keymaster

        This function capitalises the first letter of each word in SQL Server.

        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  

        Best Regards
        Peter Jones

    Viewing 0 reply threads
    • You must be logged in to reply to this topic.