SQL capitalize first letter only

Question

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

User · Answer

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   lt N gt   replace              replace                  Insurance                          lt  N gt  lt N gt                  -    - lt  N gt  lt N gt      lt  N gt   as xml  as InsXML   from  T     as X

User · Answer

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  lt 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  lt  gt  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  lt  gt  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

User · Answer

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 lt    select  length name -length replace name  -        1 from table

User · Answer

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  gt  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

[sql] SQL: capitalize first letter only

Examples related to sql

Examples related to sql-server