How to update data as upper case first letter with t-sql command?
Solution 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);
Solution 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
Solution 3
With a little help of a split function like this one.
Try this, replace YourTable
with whatever your table name is:
update T
set Name = P.Name
from YourTable as T
cross apply (select (select upper(left(X.s, 1))+lower(stuff(X.s, 1, 1, ''))+' '
from dbo.split(' ', Name) as X
for xml path(''), type).value('.', 'varchar(50)')
) as P(Name)
Solution 4
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)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @Word VARCHAR(255)
DECLARE @WordChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
DECLARE @WordIndex INT
SET @Word = ''
SET @OutputString = ''
SET @Index = 1
WHILE @Index <= LEN(@InputString)+1
BEGIN
SET @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 + @Char
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(','0','1','2','3','4','5','6','7','8','9') or @Index = LEN(@InputString)+1
BEGIN
SET @WordIndex = 1
IF LEN(@Word) > @AcronymMaxLen
BEGIN
WHILE @WordIndex <= LEN(@Word)
BEGIN
SET @WordChar = SUBSTRING(@Word,@WordIndex,1)
if @WordIndex = 1 begin
SET @Word = STUFF(@Word,@WordIndex,1,UPPER(@WordChar)) end
else begin
SET @Word = STUFF(@Word,@WordIndex,1,LOWER(@WordChar)) end
SET @WordIndex = @WordIndex + 1
END
END
ELSE BEGIN
SET @Word = UPPER(@Word)
END
set @OutputString = @OutputString + @Word
SET @Word = ''
END
SET @Word = @Word + @Char
SET @Index = @Index + 1
END
return @OutputString
end
GO
So 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
Hope it helps.
cethint
Updated on July 05, 2022Comments
-
cethint about 2 years
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)
-
My Other Me almost 12 yearsWon't that only set the first letter capital. The SO wants the first letter of every word capital as far as I can tell
-
cethint almost 12 yearssecond word's first case is lower case. I want to do all words' first cases must be upper case.
-
cethint almost 12 yearsBut this is just 2 examples. I have a lot of data on table. I can't change one by one or mechanical.
-
Mikael Eriksson almost 12 yearsOk, I removed the stuff that was just there to be able to test this.
-
Mikael Eriksson almost 12 yearsYou also need to change
'varchar(50)'
to whatever data type your Name column is. -
EJoshuaS - Stand with Ukraine over 7 yearsIt's better to include some context/explanation surrounding code as that makes the answer more useful to the OP and future readers (especially since this is an old question with several high-quality answers). Also, please be sure to use the code format feature (leave a blank line and then indent by four spaces) to improve readability.