SQL: capitalize first letter only

This question already has an answer here:

  • What’s the best way to capitalise the first letter of each word in a string in SQL Server

    5 answers

  • Finding duplicate rows in SQL Server
  • Why am I getting “Cannot Connect to Server - A network-related or instance-specific error”?
  • How can I reseed an identity column in a T-SQL table variable?
  • Netezza Incremental load from Sql server using SSIS
  • “Prevent saving changes that require the table to be re-created” negative effects
  • SSIS OPENROWSET query flat file
  • 4 Solutions collect form web for “SQL: capitalize first letter only”

    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));
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.