[FIXED] How to recreate the InitCap() function in T-SQL to apply first-letter capitalization to a string?

Issue

I have a table on my database. My table's name is "Company". I want to change data "company_name" as upper case first letter. For example;

"ABC COMPANY"

"DEF PLASTICITY"

as

"Abc Company"

"Def Plasticity"

I know that I should use "UPDATE" command. But How? Thanks for your help!

(CONCAT does not work)

Solution

SQL Server Don't have Initcap function like oracle.

You can create UDF for Initcap.

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

Checking for UDF working

select [dbo].[InitCap] ('stackoverflow com');

Stackoverflow Com

you can update your table like this

update table
set column=[dbo].[InitCap](column);

Answered By - Vishwanath Dalvi

Answer Checked By - Marilyn (PHPFixing Volunteer)