How to use initcap function in sql server?

Question

Can any one give me the syntax for intcap function in sql server 2005.

Hanook · Answer

There is no inbuilt initCap function in SQL server.. Use the follwoing user-defined function(UDF ) to get the same functionality 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 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 Reference: You can refer this link for knowing the usage of UDF http://www.sql-server-helper.com/functions/initcap.aspx

Vulpes · Answer

The Initcap function is supported by Oracle but not by SQL Server. However, you can write your own function to do the same thing. Check out this thread: http://stackoverflow.com/questions/11688182/how-to-update-data-as-upper-case-first-letter-with-t-sql-command