I need an SQL statement to capitalize the first letter of each word. The other characters have to be lower case.
The words can be like this:
wezembeek-oppem roeselare BRUGGE louvain-la-neuve Which would have to be:
Wezembeek-Oppem Roeselare Brugge Louvain-La-Neuve This should be with an UPDATE statement, I want to update the data of a column. Thank you very much for your answers in advance, I'm an SQL novice.
14 Answers
Are you asking for renaming column itself or capitalise the data inside column? If its data you've to change, then use this:
UPDATE [yourtable] SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) If you just wanted to change it only for displaying and do not need the actual data in table to change:
SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable] Hope this helps.
EDIT: I realised about the '-' so here is my attempt to solve this problem in a function.
CREATE FUNCTION [dbo].[CapitalizeFirstLetter] ( --string need to format @string VARCHAR(200)--increase the variable size depending on your needs. ) RETURNS VARCHAR(200) AS BEGIN --Declare Variables DECLARE @Index INT, @ResultString VARCHAR(200)--result string size should equal to the @string variable size --Initialize the variables SET @Index = 1 SET @ResultString = '' --Run the Loop until END of the string WHILE (@Index <LEN(@string)+1) BEGIN IF (@Index = 1)--first letter of the string BEGIN --make the first letter capital SET @ResultString = @ResultString + UPPER(SUBSTRING(@string, @Index, 1)) SET @Index = @Index+ 1--increase the index END -- IF the previous character is space or '-' or next character is '-' ELSE IF ((SUBSTRING(@string, @Index-1, 1) =' 'or SUBSTRING(@string, @Index-1, 1) ='-' or SUBSTRING(@string, @Index+1, 1) ='-') and @Index+1 <> LEN(@string)) BEGIN --make the letter capital SET @ResultString = @ResultString + UPPER(SUBSTRING(@string,@Index, 1)) SET @Index = @Index +1--increase the index END ELSE-- all others BEGIN -- make the letter simple SET @ResultString = @ResultString + LOWER(SUBSTRING(@string,@Index, 1)) SET @Index = @Index +1--incerase the index END END--END of the loop IF (@@ERROR <> 0)-- any error occur return the sEND string BEGIN SET @ResultString = @string END -- IF no error found return the new string RETURN @ResultString END So then the code would be:
UPDATE [yourtable] SET word=dbo.CapitalizeFirstLetter([STRING TO GO HERE]) Create the below function
Alter FUNCTION InitialCap(@String VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Position INT; SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin, @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin); WHILE @Position > 0 SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin, @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin); RETURN @String; END ; Then call it like
select dbo.InitialCap(columnname) from yourtable Please check the query without using a function:
declare @T table(Insurance varchar(max)) insert into @T values ('wezembeek-oppem') insert into @T values ('roeselare') insert into @T values ('BRUGGE') insert into @T values ('louvain-la-neuve') select ( select upper(T.N.value('.', 'char(1)'))+ lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+(CASE WHEN RIGHT(T.N.value('.', 'varchar(max)'), 1)='-' THEN '' ELSE ' ' END) from X.InsXML.nodes('/N') as T(N) for xml path(''), type ).value('.', 'varchar(max)') as Insurance from ( select cast('<N>'+replace( replace( Insurance, ' ', '</N><N>'), '-', '-</N><N>')+'</N>' as xml) as InsXML from @T ) as X select replace(wm_concat(new),',','-') exp_res from (select distinct initcap(substr(name,decode(level,1,1,instr(name,'-',1,level-1)+1),decode(level,(length(name)-length(replace(name,'-','')))+1,9999,instr(name,'-',1,level)-1-decode(level,1,0,instr(name,'-',1,level-1))))) new from table; connect by level<= (select (length(name)-length(replace(name,'-','')))+1 from table)); 
