How to update data as upper case first letter with t-sql command?

Question

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)

Answer 1

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);

Answer 2

update  YourTable
set     company_name = upper(substring(company_name,1,1)) + 
            lower(substring(company_name, 2, len(company_name)-1))
where   len(company_name) > 0

Live example at SQL Fiddle.