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)
Best Answer
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)ASBEGINDECLARE @Index INTDECLARE @Char CHAR(1)DECLARE @PrevChar CHAR(1)DECLARE @OutputString VARCHAR(255)SET @OutputString = LOWER(@InputString)SET @Index = 1WHILE @Index <= LEN(@InputString)BEGINSET @Char = SUBSTRING(@InputString, @Index, 1)SET @PrevChar = CASE WHEN @Index = 1 THEN ' 'ELSE SUBSTRING(@InputString, @Index - 1, 1)ENDIF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')BEGINIF @PrevChar != '''' OR UPPER(@Char) != 'S'SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))ENDSET @Index = @Index + 1ENDRETURN @OutputStringENDGOChecking for UDF working
select [dbo].[InitCap] ('stackoverflow com');Stackoverflow Comyou can update your table like this
update tableset column=[dbo].[InitCap](column);update YourTableset company_name = upper(substring(company_name,1,1)) + lower(substring(company_name, 2, len(company_name)-1))where len(company_name) > 0Live example at SQL Fiddle.
With indebtedness to the above post, this function capitalizes the first letter of every word except those that are less than a certain character length, which are assumed to be acronyms. If that's no issue, then you set the second argument to 0.
CREATE function [dbo].[f_camel_exc_short_words] (@InputString varchar(4000),@AcronymMaxLen INT )RETURNS VARCHAR(4000)ASBEGINDECLARE @Index INTDECLARE @Char CHAR(1)DECLARE @PrevChar CHAR(1)DECLARE @Word VARCHAR(255)DECLARE @WordChar CHAR(1)DECLARE @OutputString VARCHAR(255)DECLARE @WordIndex INTSET @Word = ''SET @OutputString = '' SET @Index = 1WHILE @Index <= LEN(@InputString)+1BEGINSET @Char = SUBSTRING(@InputString, @Index, 1)SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@InputString, @Index - 1, 1) END--IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')-- SET @OutputString = @OutputString + @CharIF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(','0','1','2','3','4','5','6','7','8','9') or @Index = LEN(@InputString)+1BEGINSET @WordIndex = 1IF LEN(@Word) > @AcronymMaxLen BEGINWHILE @WordIndex <= LEN(@Word)BEGINSET @WordChar = SUBSTRING(@Word,@WordIndex,1)if @WordIndex = 1 beginSET @Word = STUFF(@Word,@WordIndex,1,UPPER(@WordChar)) endelse beginSET @Word = STUFF(@Word,@WordIndex,1,LOWER(@WordChar)) endSET @WordIndex = @WordIndex + 1ENDENDELSE BEGINSET @Word = UPPER(@Word)ENDset @OutputString = @OutputString + @WordSET @Word = ''ENDSET @Word = @Word + @CharSET @Index = @Index + 1END return @OutputStringendGOSo
PRINT dbo.f_camel_exc_short_words ('PIONEER EURO BOND FUND CLASS C (NON-DIST) (EUR) (OFFSHORE) ISIN LU0119429891',4)returns
Pioneer EURO BOND FUND Class C (NON-Dist) (EUR) (Offshore) ISIN LU0119429891
With a little help of a split function like this one.
Try this, replace YourTable with whatever your table name is:
update Tset Name = P.Namefrom YourTable as Tcross apply (select (select upper(left(X.s, 1))+lower(stuff(X.s, 1, 1, ''))+' 'from dbo.split(' ', Name) as Xfor xml path(''), type).value('.', 'varchar(50)')) as P(Name)Try this:
declare @word as nvarchar (50)set @word = 'ABC COMPANY'select upper(left(@word, 1)) + lower(SUBSTRING(@word,2,charindex(' ', @word)-2)) + ' ' + upper(left(substring(@word,charindex(' ', @word)+1,len(@word)-1),1)) + lower(SUBSTRING(@word,charindex(' ', @word)+2, len(@word)))A further modification handles possessives ('s) and words starting with Mc
if ' ' + @OutputString like '% Mc%'set @OutputString = ' ' + @OutputStringset @index = CHARINDEX ( ' Mc', @OutputString)while @Index > 0beginset @OutputString = SUBSTRING(@outputString, 1, @index + 2) + UPPER(SUBSTRING(@outputString, @index + 3, 1)) + SUBSTRING(@outputString, @index + 4, len(@outputString))set @index = CHARINDEX ( ' Mc', @OutputString, @Index + 4)endset @outputstring = ltrim(rtrim(@outputstring))if @OutputString + ' ' like '%''S %' set @OutputString = ltrim(rtrim(REPLACE(@outputstring + ' ', '''S ', '''s ')))place right before Return
CREATE FUNCTION Initcap( @mystring varchar(50) )RETURNS VARCHAR(50)ASBEGINDECLARE @val VARCHAR(50);SET @val = (select upper(left(@mystring,1)) + lower(substring(@mystring,2,datalength(@mystring)-1)) )RETURN @val;END;