SQL Server Make all UPPER case to Proper Case Title Case

Question

I have a table that was imported as all UPPER CASE and I would like to turn it into Proper Case  What script have any of you used to complete this

User · Answer

I am a little late in the game  but I believe this is more functional and it works with any language  including Russian  German  Thai  Vietnamese etc  It will make uppercase anything after   or - or   or   or   or space  obviously      CREATE FUNCTION  dbo   fnToProperCase    name nvarchar 500    RETURNS nvarchar 500  AS BEGIN declare  pos    int   1          pos2   int  if   name  lt  gt     --or  name   lower  name  collate SQL Latin1 General CP1 CS AS or  name   upper  name  collate SQL Latin1 General CP1 CS AS  begin     set  name   lower rtrim  name       while  1   1      begin         set  name   stuff  name   pos  1  upper substring  name   pos  1            set  pos2   patindex    -           substring  name   pos  500           set  pos     pos2         if  isnull  pos2  0    0 or  pos  gt  len  name               break     end end  return  name END GO

User · Answer

This worked in SSMS   Select Jobtitle  concat Upper LEFT jobtitle 1    SUBSTRING jobtitle 2 LEN jobtitle    as Propercase From  HumanResources   Employee

User · Answer

The link I posted above is a great option that addresses the main issue  that we can never programmatically account for all cases  Smith-Jones  von Haussen  John Smith M D    at least not in an elegant manner   Tony introduces the concept of an exception   break character to deal with these cases  Anyways  building on Cervo s idea  upper all lower chars preceded by space   the replace statements could be wrapped up in a single table based replace instead  Really  any low up character combination could be inserted into  alpha and the statement would not change     declare  str    nvarchar 8000  declare  alpha  table  low nchar 1   up nchar 1     set  str    ALL UPPER CASE and    SOME lower                          CC        -- stage the alpha  needs number table  insert into  alpha     -- A-Z   a-z     select      nchar n 32                   nchar n      from        dbo Number     where       n between 65 and 90 or                 n between 192 and 223  -- append space at start of str set  str   lower        str   -- upper all lower case chars preceded by space select   str   replace  str        low        up   from     Alpha  select  str

User · Answer

I know the devil is in the detail  especially where people s personal data is concerned   and that it would be very nice to have properly capitalised names  but the above kind of hassle is why the pragmatic  time-conscious amongst us use the following    SELECT UPPER  Put YoUR O So oddLy casED McWeird-nAme von rightHERE here    In my experience  people are fine seeing THEIR NAME     even when it s half way through a sentence   Refer to  the Russians used a pencil

User · Answer

If you can enable the CLR in SQL Server  requires 2005 or later  then you could create a CLR function that uses the TextInfo ToTitleCase built-in function which would allow you to create a culture-aware way of doing this in only a few lines of code

User · Answer

It would make sense to maintain a lookup of exceptions to take care of The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s

User · Answer

Here s a UDF that will do the trick     create function ProperCase  Text as varchar 8000   returns varchar 8000  as begin   declare  Reset bit    declare  Ret varchar 8000     declare  i int    declare  c char 1      if  Text is null     return null     select  Reset   1   i   1   Ret          while   i  lt   len  Text       select  c   substring  Text   i  1          Ret    Ret   case when  Reset   1 then UPPER  c  else LOWER  c  end         Reset   case when  c like   a-zA-Z   then 0 else 1 end         i    i   1   return  Ret end   You will still have to use it to update your data though

User · Answer

Just learned about InitCap      Here is some sample code   SELECT ID        InitCap LastName          FirstName         Nvl MiddleName      AS RecipientName FROM SomeTable

User · Answer

UPDATE titles   SET title         UPPER LEFT title  1             LOWER RIGHT title  LEN title  - 1     http   sqlmag com t-sql how-title-case-column-value

User · Answer

If you can enable the CLR in SQL Server  requires 2005 or later  then you could create a CLR function that uses the TextInfo ToTitleCase built-in function which would allow you to create a culture-aware way of doing this in only a few lines of code

User · Answer

Borrowed and improved on  Richard Sayakanit answer  This handles multiple words  Like his answer  this doesn t use any UDFs  only built-in functions  STRING SPLIT and STRING AGG  and it s pretty fast  STRING AGG requires SQL Server 2017 but you can always use the STUFF XML trick  Won t handle every exception but can work great for many requirements    SELECT StateName    North Carolina   INTO  States UNION ALL SELECT  Texas     WITH cteData AS        SELECT          UPPER LEFT value  1                 LOWER RIGHT value  LEN value  - 1   value  op StateName     FROM    States op     CROSS APPLY STRING SPLIT op StateName       AS ss   SELECT      STRING AGG value       FROM cteData c  GROUP BY StateName

User · Answer

If you re in SSIS importing data that has mixed cased and need to do a lookup on a column with proper case  you ll notice that the lookup fails where the source is mixed and the lookup source is proper   You ll also notice you can t use the right and left functions is SSIS for SQL Server 2008r2 for derived columns  Here s a solution that works for me    UPPER substring input column name 1 1     LOWER substring input column name  2  len input column name -1

User · Answer

If you re in SSIS importing data that has mixed cased and need to do a lookup on a column with proper case  you ll notice that the lookup fails where the source is mixed and the lookup source is proper   You ll also notice you can t use the right and left functions is SSIS for SQL Server 2008r2 for derived columns  Here s a solution that works for me    UPPER substring input column name 1 1     LOWER substring input column name  2  len input column name -1

User · Answer

If you know all the data is just a single word here s a solution  First update the column to all lower and then run the following      update tableName set columnName        upper SUBSTRING columnName  1  1     substring columnName  2  len columnName   from tableName

User · Answer

Copy and paste your data into MS Word and use built in text-conversion to  Capitalize Each Word    Compare against your original data to address exceptions  Can t see any way around manually sidestepping  MacDonald  and  IBM  type exceptions but this was how I got it done FWIW

User · Answer

I think you will find that the following is more efficient   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str     SET  str   REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE   str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z       RETURN RIGHT  str  LEN  str  - 1  END GO   The replace statement could be cut and pasted directly into a SQL query   It is ultra ugly  however by replacing  str with the column you are interested in  you will not pay a price for an implicit cursor like you will with the udfs thus posted   I find that even using my UDF it is much more efficient   Oh and instead of generating the replace statement by hand use this   -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000  SET  x   0 SET  sql     str  -- actual variable column you want to replace WHILE  x  lt  26 BEGIN     SET  c   CHAR ASCII  a      x      SET  sql    REPLACE      sql              c               UPPER  c              SET  x    x   1 END PRINT  sql   Anyway it depends on the number of rows   I wish  you could just do s  b  a-z   uc  1   but oh well we work with the tools we have   NOTE you would have to use this as you would have to use it as    SELECT dbo ProperCase LOWER column   since the column is in uppercase   It actually works pretty fast on my table of 5 000 entries  not even one second  even with the lower   In response to the flurry of comments regarding internationalization I present the following implementation that handles every ascii character relying only on SQL Server s Implementation of upper and lower   Remember  the variables we are using here are VARCHAR which means that they can only hold ASCII values   In order to use further international alphabets  you have to use NVARCHAR   The logic would be similar but you would need to use UNICODE and NCHAR in place of ASCII AND CHAR and the replace statement would be much more huge      -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000        count  INT SEt  x   0 SET  count   0 SET  sql     str  -- actual variable you want to replace WHILE  x  lt  256 BEGIN     SET  c   CHAR  x      -- Only generate replacement expression for characters where upper and lowercase differ     IF  x   ASCII LOWER  c   AND  x    ASCII UPPER  c       BEGIN         SET  sql    REPLACE      sql              c               UPPER  c                  SET  count    count   1     END     SET  x    x   1 END PRINT  sql PRINT  Total characters substituted      CONVERT VARCHAR 255    count    Basically the premise of the my method is trading pre-computing for efficiency   The full ASCII implementation is as follows   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str SET  str       REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE  str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     RETURN RIGHT  str  LEN  str  - 1  END GO

User · Answer

I know this is late post in this thread but  worth looking  This function works for me ever time  So thought of sharing it   CREATE FUNCTION  dbo   fnConvert TitleCase    InputString VARCHAR 4000    RETURNS VARCHAR 4000  AS BEGIN DECLARE  Index INT DECLARE  Char CHAR 1  DECLARE  OutputString VARCHAR 255   SET  OutputString   LOWER  InputString  SET  Index   2 SET  OutputString   STUFF  OutputString  1  1 UPPER SUBSTRING  InputString 1 1     WHILE  Index  lt   LEN  InputString  BEGIN     SET  Char   SUBSTRING  InputString   Index  1      IF  Char IN                                           -          amp                 IF  Index   1  lt   LEN  InputString  BEGIN     IF  Char             OR     UPPER SUBSTRING  InputString   Index   1  1       S      SET  OutputString       STUFF  OutputString   Index   1  1 UPPER SUBSTRING  InputString   Index   1  1    END     SET  Index    Index   1 END  RETURN ISNULL  OutputString     END   Test calls   select dbo fnConvert TitleCase Upper                           CC         as test select dbo fnConvert TitleCase upper  Whatever the mind of man can conceive and believe  it can achieve      Napoleon hill    as test   Results

User · Answer

I think you will find that the following is more efficient   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str     SET  str   REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE   str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z       RETURN RIGHT  str  LEN  str  - 1  END GO   The replace statement could be cut and pasted directly into a SQL query   It is ultra ugly  however by replacing  str with the column you are interested in  you will not pay a price for an implicit cursor like you will with the udfs thus posted   I find that even using my UDF it is much more efficient   Oh and instead of generating the replace statement by hand use this   -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000  SET  x   0 SET  sql     str  -- actual variable column you want to replace WHILE  x  lt  26 BEGIN     SET  c   CHAR ASCII  a      x      SET  sql    REPLACE      sql              c               UPPER  c              SET  x    x   1 END PRINT  sql   Anyway it depends on the number of rows   I wish  you could just do s  b  a-z   uc  1   but oh well we work with the tools we have   NOTE you would have to use this as you would have to use it as    SELECT dbo ProperCase LOWER column   since the column is in uppercase   It actually works pretty fast on my table of 5 000 entries  not even one second  even with the lower   In response to the flurry of comments regarding internationalization I present the following implementation that handles every ascii character relying only on SQL Server s Implementation of upper and lower   Remember  the variables we are using here are VARCHAR which means that they can only hold ASCII values   In order to use further international alphabets  you have to use NVARCHAR   The logic would be similar but you would need to use UNICODE and NCHAR in place of ASCII AND CHAR and the replace statement would be much more huge      -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000        count  INT SEt  x   0 SET  count   0 SET  sql     str  -- actual variable you want to replace WHILE  x  lt  256 BEGIN     SET  c   CHAR  x      -- Only generate replacement expression for characters where upper and lowercase differ     IF  x   ASCII LOWER  c   AND  x    ASCII UPPER  c       BEGIN         SET  sql    REPLACE      sql              c               UPPER  c                  SET  count    count   1     END     SET  x    x   1 END PRINT  sql PRINT  Total characters substituted      CONVERT VARCHAR 255    count    Basically the premise of the my method is trading pre-computing for efficiency   The full ASCII implementation is as follows   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str SET  str       REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE  str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     RETURN RIGHT  str  LEN  str  - 1  END GO

User · Answer

Recently had to tackle this and came up with the following after nothing quite hit everything I wanted  This will do an entire sentence  cases for special word handling  We also had issues with single character  words  that a lot of the simpler methods handle but not the more complicated methods  Single return variable  no loops or cursors either   CREATE FUNCTION ProperCase  Text AS NVARCHAR MAX   RETURNS NVARCHAR MAX  AS BEGIN      DECLARE  return NVARCHAR MAX       SELECT  return   COALESCE  return              Word FROM            SELECT CASE             WHEN LOWER value     llc  THEN UPPER value              WHEN LOWER value     lp  THEN UPPER value  --Add as many new special cases as needed             ELSE                CASE WHEN LEN value    1                THEN UPPER value                 ELSE UPPER LEFT value  1      LOWER RIGHT value  LEN value  - 1                  END             END AS Word          FROM STRING SPLIT  Text              tmp       RETURN  return END

User · Answer

This worked in SSMS   Select Jobtitle  concat Upper LEFT jobtitle 1    SUBSTRING jobtitle 2 LEN jobtitle    as Propercase From  HumanResources   Employee

User · Answer

I am a little late in the game  but I believe this is more functional and it works with any language  including Russian  German  Thai  Vietnamese etc  It will make uppercase anything after   or - or   or   or   or space  obviously      CREATE FUNCTION  dbo   fnToProperCase    name nvarchar 500    RETURNS nvarchar 500  AS BEGIN declare  pos    int   1          pos2   int  if   name  lt  gt     --or  name   lower  name  collate SQL Latin1 General CP1 CS AS or  name   upper  name  collate SQL Latin1 General CP1 CS AS  begin     set  name   lower rtrim  name       while  1   1      begin         set  name   stuff  name   pos  1  upper substring  name   pos  1            set  pos2   patindex    -           substring  name   pos  500           set  pos     pos2         if  isnull  pos2  0    0 or  pos  gt  len  name               break     end end  return  name END GO

User · Answer

This function     Proper Cases  all  UPPER CASE  words that are delimited by white space leaves  lower case words  alone works properly even for non-English alphabets is portable in that it does not use fancy features of recent SQL server versions can be easily changed to use NCHAR and NVARCHAR for unicode support as well as any parameter length you see fit white space definition can be configured     CREATE FUNCTION ToProperCase  string VARCHAR 255   RETURNS VARCHAR 255  AS BEGIN   DECLARE  i INT           -- index   DECLARE  l INT           -- input length   DECLARE  c NCHAR 1       -- current char   DECLARE  f INT           -- first letter flag  1 0    DECLARE  o VARCHAR 255   -- output string   DECLARE  w VARCHAR 10    -- characters considered as white space    SET  w         CHAR 13    CHAR 10    CHAR 9    CHAR 160                SET  i   1   SET  l   LEN  string    SET  f   1   SET  o         WHILE  i  lt    l   BEGIN     SET  c   SUBSTRING  string   i  1      IF  f   1      BEGIN      SET  o    o    c      SET  f   0     END     ELSE     BEGIN      SET  o    o   LOWER  c      END      IF  c LIKE  w SET  f   1      SET  i    i   1   END    RETURN  o END   Result   dbo ToProperCase  ALL UPPER CASE and    SOME lower                          CC        ----------------------------------------------------------------- All Upper Case and      Some lower                          Cc

User · Answer

I know the devil is in the detail  especially where people s personal data is concerned   and that it would be very nice to have properly capitalised names  but the above kind of hassle is why the pragmatic  time-conscious amongst us use the following    SELECT UPPER  Put YoUR O So oddLy casED McWeird-nAme von rightHERE here    In my experience  people are fine seeing THEIR NAME     even when it s half way through a sentence   Refer to  the Russians used a pencil

User · Answer

Here s a UDF that will do the trick     create function ProperCase  Text as varchar 8000   returns varchar 8000  as begin   declare  Reset bit    declare  Ret varchar 8000     declare  i int    declare  c char 1      if  Text is null     return null     select  Reset   1   i   1   Ret          while   i  lt   len  Text       select  c   substring  Text   i  1          Ret    Ret   case when  Reset   1 then UPPER  c  else LOWER  c  end         Reset   case when  c like   a-zA-Z   then 0 else 1 end         i    i   1   return  Ret end   You will still have to use it to update your data though

User · Answer

If you can enable the CLR in SQL Server  requires 2005 or later  then you could create a CLR function that uses the TextInfo ToTitleCase built-in function which would allow you to create a culture-aware way of doing this in only a few lines of code

User · Answer

Is it too late to go back and get the un-uppercased data    The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s of your client base may not like the result of your processing     Also  I m guessing that this is intended to be a one-time upgrade of the data   It might be easier to export  filter modify  and re-import the corrected names into the db  and then you can use non-SQL approaches to name fixing

User · Answer

The link I posted above is a great option that addresses the main issue  that we can never programmatically account for all cases  Smith-Jones  von Haussen  John Smith M D    at least not in an elegant manner   Tony introduces the concept of an exception   break character to deal with these cases  Anyways  building on Cervo s idea  upper all lower chars preceded by space   the replace statements could be wrapped up in a single table based replace instead  Really  any low up character combination could be inserted into  alpha and the statement would not change     declare  str    nvarchar 8000  declare  alpha  table  low nchar 1   up nchar 1     set  str    ALL UPPER CASE and    SOME lower                          CC        -- stage the alpha  needs number table  insert into  alpha     -- A-Z   a-z     select      nchar n 32                   nchar n      from        dbo Number     where       n between 65 and 90 or                 n between 192 and 223  -- append space at start of str set  str   lower        str   -- upper all lower case chars preceded by space select   str   replace  str        low        up   from     Alpha  select  str

User · Answer

This function     Proper Cases  all  UPPER CASE  words that are delimited by white space leaves  lower case words  alone works properly even for non-English alphabets is portable in that it does not use fancy features of recent SQL server versions can be easily changed to use NCHAR and NVARCHAR for unicode support as well as any parameter length you see fit white space definition can be configured     CREATE FUNCTION ToProperCase  string VARCHAR 255   RETURNS VARCHAR 255  AS BEGIN   DECLARE  i INT           -- index   DECLARE  l INT           -- input length   DECLARE  c NCHAR 1       -- current char   DECLARE  f INT           -- first letter flag  1 0    DECLARE  o VARCHAR 255   -- output string   DECLARE  w VARCHAR 10    -- characters considered as white space    SET  w         CHAR 13    CHAR 10    CHAR 9    CHAR 160                SET  i   1   SET  l   LEN  string    SET  f   1   SET  o         WHILE  i  lt    l   BEGIN     SET  c   SUBSTRING  string   i  1      IF  f   1      BEGIN      SET  o    o    c      SET  f   0     END     ELSE     BEGIN      SET  o    o   LOWER  c      END      IF  c LIKE  w SET  f   1      SET  i    i   1   END    RETURN  o END   Result   dbo ToProperCase  ALL UPPER CASE and    SOME lower                          CC        ----------------------------------------------------------------- All Upper Case and      Some lower                          Cc

User · Answer

Here is another variation I found on the SQLTeam com Forums   http   www sqlteam com forums topic asp TOPIC ID 47718  create FUNCTION PROPERCASE   --The string to be converted to proper case  input varchar 8000    --This function returns the proper case string of varchar type RETURNS varchar 8000  AS BEGIN IF  input IS NULL BEGIN --Just return NULL if input string is NULL RETURN NULL END  --Character variable declarations DECLARE  output varchar 8000  --Integer variable declarations DECLARE  ctr int   len int   found at int --Constant declarations DECLARE  LOWER CASE a int   LOWER CASE z int   Delimiter char 3    UPPER CASE A int   UPPER CASE Z int  --Variable Constant initializations SET  ctr   1 SET  len   LEN  input  SET  output      SET  LOWER CASE a   97 SET  LOWER CASE z   122 SET  Delimiter      -  SET  UPPER CASE A   65 SET  UPPER CASE Z   90  WHILE  ctr  lt    len BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX SUBSTRING  input  ctr 1    Delimiter   gt  0 BEGIN SET  output    output   SUBSTRING  input  ctr 1  SET  ctr    ctr   1 END  IF ASCII SUBSTRING  input  ctr 1   BETWEEN  LOWER CASE a AND  LOWER CASE z BEGIN --Converting the first character to upper case SET  output    output   UPPER SUBSTRING  input  ctr 1   END ELSE BEGIN SET  output    output   SUBSTRING  input  ctr 1  END  SET  ctr    ctr   1  WHILE CHARINDEX SUBSTRING  input  ctr 1    Delimiter    0 AND   ctr  lt    len  BEGIN IF ASCII SUBSTRING  input  ctr 1   BETWEEN  UPPER CASE A AND  UPPER CASE Z BEGIN SET  output    output   LOWER SUBSTRING  input  ctr 1   END ELSE BEGIN SET  output    output   SUBSTRING  input  ctr 1  END SET  ctr    ctr   1 END  END RETURN  output END    GO SET QUOTED IDENTIFIER OFF GO SET ANSI NULLS ON GO

User · Answer

Here is another variation I found on the SQLTeam com Forums   http   www sqlteam com forums topic asp TOPIC ID 47718  create FUNCTION PROPERCASE   --The string to be converted to proper case  input varchar 8000    --This function returns the proper case string of varchar type RETURNS varchar 8000  AS BEGIN IF  input IS NULL BEGIN --Just return NULL if input string is NULL RETURN NULL END  --Character variable declarations DECLARE  output varchar 8000  --Integer variable declarations DECLARE  ctr int   len int   found at int --Constant declarations DECLARE  LOWER CASE a int   LOWER CASE z int   Delimiter char 3    UPPER CASE A int   UPPER CASE Z int  --Variable Constant initializations SET  ctr   1 SET  len   LEN  input  SET  output      SET  LOWER CASE a   97 SET  LOWER CASE z   122 SET  Delimiter      -  SET  UPPER CASE A   65 SET  UPPER CASE Z   90  WHILE  ctr  lt    len BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX SUBSTRING  input  ctr 1    Delimiter   gt  0 BEGIN SET  output    output   SUBSTRING  input  ctr 1  SET  ctr    ctr   1 END  IF ASCII SUBSTRING  input  ctr 1   BETWEEN  LOWER CASE a AND  LOWER CASE z BEGIN --Converting the first character to upper case SET  output    output   UPPER SUBSTRING  input  ctr 1   END ELSE BEGIN SET  output    output   SUBSTRING  input  ctr 1  END  SET  ctr    ctr   1  WHILE CHARINDEX SUBSTRING  input  ctr 1    Delimiter    0 AND   ctr  lt    len  BEGIN IF ASCII SUBSTRING  input  ctr 1   BETWEEN  UPPER CASE A AND  UPPER CASE Z BEGIN SET  output    output   LOWER SUBSTRING  input  ctr 1   END ELSE BEGIN SET  output    output   SUBSTRING  input  ctr 1  END SET  ctr    ctr   1 END  END RETURN  output END    GO SET QUOTED IDENTIFIER OFF GO SET ANSI NULLS ON GO

User · Answer

On Server Server 2016 and newer  you can use STRING SPLIT  with t as       select  GOOFYEAR Tire and Rubber Company  as n     union all     select  THE HAPPY BEAR  as n     union all     select  MONK HOUSE SALES  as n     union all     select  FORUM COMMUNICATIONS  as n   select     n                select                     upper left value  1                 lower substring value  2  999                     from               select value             from string split t n                 as sq         for xml path            as title cased from t  Example

User · Answer

Is it too late to go back and get the un-uppercased data    The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s of your client base may not like the result of your processing     Also  I m guessing that this is intended to be a one-time upgrade of the data   It might be easier to export  filter modify  and re-import the corrected names into the db  and then you can use non-SQL approaches to name fixing

User · Answer

Just learned about InitCap      Here is some sample code   SELECT ID        InitCap LastName          FirstName         Nvl MiddleName      AS RecipientName FROM SomeTable

User · Answer

It would make sense to maintain a lookup of exceptions to take care of The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s

User · Answer

A slight modification to  Galwegian s answer - which turns e g  St Elizabeth s into St Elizabeth S   This modification keeps apostrophe-s as lowercase where the s comes at the end of the string provided or the s is followed by a space  and only in those circumstances    create function properCase  text as varchar 8000   returns varchar 8000  as begin     declare  reset int      declare  ret varchar 8000       declare  i int      declare  c char 1       declare  d char 1        if  text is null     return null       select  reset   1   i   1   ret            while   i  lt   len  text       select          c   substring  text   i  1            d   substring  text   i 1  1            ret    ret   case when  reset   1 or   reset -1 and  c   s   or   reset -1 and  c  s  and  d       then upper  c  else lower  c  end           reset   case when  c like   a-za-z   then 0 when  c      then -1 else 1 end           i    i   1     return  ret end   It turns    st elizabeth s into St Elizabeth s o keefe into O Keefe o sullivan into O Sullivan   Others  comments that different solutions are preferable for non-English input remain the case

User · Answer

A slight modification to  Galwegian s answer - which turns e g  St Elizabeth s into St Elizabeth S   This modification keeps apostrophe-s as lowercase where the s comes at the end of the string provided or the s is followed by a space  and only in those circumstances    create function properCase  text as varchar 8000   returns varchar 8000  as begin     declare  reset int      declare  ret varchar 8000       declare  i int      declare  c char 1       declare  d char 1        if  text is null     return null       select  reset   1   i   1   ret            while   i  lt   len  text       select          c   substring  text   i  1            d   substring  text   i 1  1            ret    ret   case when  reset   1 or   reset -1 and  c   s   or   reset -1 and  c  s  and  d       then upper  c  else lower  c  end           reset   case when  c like   a-za-z   then 0 when  c      then -1 else 1 end           i    i   1     return  ret end   It turns    st elizabeth s into St Elizabeth s o keefe into O Keefe o sullivan into O Sullivan   Others  comments that different solutions are preferable for non-English input remain the case

User · Answer

Here is a version that uses a sequence or numbers table rather than a loop   You can modify the WHERE clause to suite your personal rules for when to convert a character to upper case   I have just included a simple set that will upper case any letter that is proceeded by a non-letter with the exception of apostrophes   This does how ever mean that 123apple would have a match on the  a  because  3  is not a letter   If you want just white-space  space  tab  carriage-return  line-feed   you can replace the pattern    a-z   with       Char 32    Char 9    Char 13    Char 10            CREATE FUNCTION String InitCap   string nvarchar 4000    RETURNS nvarchar 4000  AS BEGIN  -- 1  Convert all letters to lower case     DECLARE  InitCap nvarchar 4000   SET  InitCap   Lower  string    -- 2  Using a Sequence  replace the letters that should be upper case with their upper case version     SELECT  InitCap   Stuff   InitCap  n  1  Upper  SubString   InitCap  n  1           FROM           SELECT  1   n1 n   n10 n   n100 n   n1000 n  AS n         FROM        SELECT 0 AS n UNION SELECT    1 UNION SELECT    2 UNION SELECT    3 UNION SELECT    4 UNION SELECT    5 UNION SELECT    6 UNION SELECT    7 UNION SELECT    8 UNION SELECT    9  AS    n1         CROSS JOIN  SELECT 0 AS n UNION SELECT   10 UNION SELECT   20 UNION SELECT   30 UNION SELECT   40 UNION SELECT   50 UNION SELECT   60 UNION SELECT   70 UNION SELECT   80 UNION SELECT   90  AS   n10         CROSS JOIN  SELECT 0 AS n UNION SELECT  100 UNION SELECT  200 UNION SELECT  300 UNION SELECT  400 UNION SELECT  500 UNION SELECT  600 UNION SELECT  700 UNION SELECT  800 UNION SELECT  900  AS  n100         CROSS JOIN  SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000                                                                                                              AS n1000           AS Sequence     WHERE          n BETWEEN 1 AND Len   InitCap       AND SubString   InitCap  n  1   LIKE   a-z                      this character is a letter        AND           n   1                                                       this character is the first  character             OR SubString   InitCap  n-1  1   LIKE    a-z                the previous character is NOT a letter                  AND           n  lt  3                                                       only test the 3rd or greater characters for this exception            OR SubString   InitCap  n-2  3   NOT LIKE   a-z    a-z      exception  The pattern  lt letter gt   lt letter gt  should not capatolize the letter following the apostrophy               -- 3  Return the modified version of the input     RETURN  InitCap  END

User · Answer

The link I posted above is a great option that addresses the main issue  that we can never programmatically account for all cases  Smith-Jones  von Haussen  John Smith M D    at least not in an elegant manner   Tony introduces the concept of an exception   break character to deal with these cases  Anyways  building on Cervo s idea  upper all lower chars preceded by space   the replace statements could be wrapped up in a single table based replace instead  Really  any low up character combination could be inserted into  alpha and the statement would not change     declare  str    nvarchar 8000  declare  alpha  table  low nchar 1   up nchar 1     set  str    ALL UPPER CASE and    SOME lower                          CC        -- stage the alpha  needs number table  insert into  alpha     -- A-Z   a-z     select      nchar n 32                   nchar n      from        dbo Number     where       n between 65 and 90 or                 n between 192 and 223  -- append space at start of str set  str   lower        str   -- upper all lower case chars preceded by space select   str   replace  str        low        up   from     Alpha  select  str

User · Answer

This function     Proper Cases  all  UPPER CASE  words that are delimited by white space leaves  lower case words  alone works properly even for non-English alphabets is portable in that it does not use fancy features of recent SQL server versions can be easily changed to use NCHAR and NVARCHAR for unicode support as well as any parameter length you see fit white space definition can be configured     CREATE FUNCTION ToProperCase  string VARCHAR 255   RETURNS VARCHAR 255  AS BEGIN   DECLARE  i INT           -- index   DECLARE  l INT           -- input length   DECLARE  c NCHAR 1       -- current char   DECLARE  f INT           -- first letter flag  1 0    DECLARE  o VARCHAR 255   -- output string   DECLARE  w VARCHAR 10    -- characters considered as white space    SET  w         CHAR 13    CHAR 10    CHAR 9    CHAR 160                SET  i   1   SET  l   LEN  string    SET  f   1   SET  o         WHILE  i  lt    l   BEGIN     SET  c   SUBSTRING  string   i  1      IF  f   1      BEGIN      SET  o    o    c      SET  f   0     END     ELSE     BEGIN      SET  o    o   LOWER  c      END      IF  c LIKE  w SET  f   1      SET  i    i   1   END    RETURN  o END   Result   dbo ToProperCase  ALL UPPER CASE and    SOME lower                          CC        ----------------------------------------------------------------- All Upper Case and      Some lower                          Cc

User · Answer

I think you will find that the following is more efficient   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str     SET  str   REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE   str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z       RETURN RIGHT  str  LEN  str  - 1  END GO   The replace statement could be cut and pasted directly into a SQL query   It is ultra ugly  however by replacing  str with the column you are interested in  you will not pay a price for an implicit cursor like you will with the udfs thus posted   I find that even using my UDF it is much more efficient   Oh and instead of generating the replace statement by hand use this   -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000  SET  x   0 SET  sql     str  -- actual variable column you want to replace WHILE  x  lt  26 BEGIN     SET  c   CHAR ASCII  a      x      SET  sql    REPLACE      sql              c               UPPER  c              SET  x    x   1 END PRINT  sql   Anyway it depends on the number of rows   I wish  you could just do s  b  a-z   uc  1   but oh well we work with the tools we have   NOTE you would have to use this as you would have to use it as    SELECT dbo ProperCase LOWER column   since the column is in uppercase   It actually works pretty fast on my table of 5 000 entries  not even one second  even with the lower   In response to the flurry of comments regarding internationalization I present the following implementation that handles every ascii character relying only on SQL Server s Implementation of upper and lower   Remember  the variables we are using here are VARCHAR which means that they can only hold ASCII values   In order to use further international alphabets  you have to use NVARCHAR   The logic would be similar but you would need to use UNICODE and NCHAR in place of ASCII AND CHAR and the replace statement would be much more huge      -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000        count  INT SEt  x   0 SET  count   0 SET  sql     str  -- actual variable you want to replace WHILE  x  lt  256 BEGIN     SET  c   CHAR  x      -- Only generate replacement expression for characters where upper and lowercase differ     IF  x   ASCII LOWER  c   AND  x    ASCII UPPER  c       BEGIN         SET  sql    REPLACE      sql              c               UPPER  c                  SET  count    count   1     END     SET  x    x   1 END PRINT  sql PRINT  Total characters substituted      CONVERT VARCHAR 255    count    Basically the premise of the my method is trading pre-computing for efficiency   The full ASCII implementation is as follows   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str SET  str       REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE  str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     RETURN RIGHT  str  LEN  str  - 1  END GO

User · Answer

If you know all the data is just a single word here s a solution  First update the column to all lower and then run the following      update tableName set columnName        upper SUBSTRING columnName  1  1     substring columnName  2  len columnName   from tableName

User · Answer

Is it too late to go back and get the un-uppercased data    The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s of your client base may not like the result of your processing     Also  I m guessing that this is intended to be a one-time upgrade of the data   It might be easier to export  filter modify  and re-import the corrected names into the db  and then you can use non-SQL approaches to name fixing

User · Answer

Here s a UDF that will do the trick     create function ProperCase  Text as varchar 8000   returns varchar 8000  as begin   declare  Reset bit    declare  Ret varchar 8000     declare  i int    declare  c char 1      if  Text is null     return null     select  Reset   1   i   1   Ret          while   i  lt   len  Text       select  c   substring  Text   i  1          Ret    Ret   case when  Reset   1 then UPPER  c  else LOWER  c  end         Reset   case when  c like   a-zA-Z   then 0 else 1 end         i    i   1   return  Ret end   You will still have to use it to update your data though

User · Answer

Recently had to tackle this and came up with the following after nothing quite hit everything I wanted  This will do an entire sentence  cases for special word handling  We also had issues with single character  words  that a lot of the simpler methods handle but not the more complicated methods  Single return variable  no loops or cursors either   CREATE FUNCTION ProperCase  Text AS NVARCHAR MAX   RETURNS NVARCHAR MAX  AS BEGIN      DECLARE  return NVARCHAR MAX       SELECT  return   COALESCE  return              Word FROM            SELECT CASE             WHEN LOWER value     llc  THEN UPPER value              WHEN LOWER value     lp  THEN UPPER value  --Add as many new special cases as needed             ELSE                CASE WHEN LEN value    1                THEN UPPER value                 ELSE UPPER LEFT value  1      LOWER RIGHT value  LEN value  - 1                  END             END AS Word          FROM STRING SPLIT  Text              tmp       RETURN  return END

User · Answer

The link I posted above is a great option that addresses the main issue  that we can never programmatically account for all cases  Smith-Jones  von Haussen  John Smith M D    at least not in an elegant manner   Tony introduces the concept of an exception   break character to deal with these cases  Anyways  building on Cervo s idea  upper all lower chars preceded by space   the replace statements could be wrapped up in a single table based replace instead  Really  any low up character combination could be inserted into  alpha and the statement would not change     declare  str    nvarchar 8000  declare  alpha  table  low nchar 1   up nchar 1     set  str    ALL UPPER CASE and    SOME lower                          CC        -- stage the alpha  needs number table  insert into  alpha     -- A-Z   a-z     select      nchar n 32                   nchar n      from        dbo Number     where       n between 65 and 90 or                 n between 192 and 223  -- append space at start of str set  str   lower        str   -- upper all lower case chars preceded by space select   str   replace  str        low        up   from     Alpha  select  str

User · Answer

This function     Proper Cases  all  UPPER CASE  words that are delimited by white space leaves  lower case words  alone works properly even for non-English alphabets is portable in that it does not use fancy features of recent SQL server versions can be easily changed to use NCHAR and NVARCHAR for unicode support as well as any parameter length you see fit white space definition can be configured     CREATE FUNCTION ToProperCase  string VARCHAR 255   RETURNS VARCHAR 255  AS BEGIN   DECLARE  i INT           -- index   DECLARE  l INT           -- input length   DECLARE  c NCHAR 1       -- current char   DECLARE  f INT           -- first letter flag  1 0    DECLARE  o VARCHAR 255   -- output string   DECLARE  w VARCHAR 10    -- characters considered as white space    SET  w         CHAR 13    CHAR 10    CHAR 9    CHAR 160                SET  i   1   SET  l   LEN  string    SET  f   1   SET  o         WHILE  i  lt    l   BEGIN     SET  c   SUBSTRING  string   i  1      IF  f   1      BEGIN      SET  o    o    c      SET  f   0     END     ELSE     BEGIN      SET  o    o   LOWER  c      END      IF  c LIKE  w SET  f   1      SET  i    i   1   END    RETURN  o END   Result   dbo ToProperCase  ALL UPPER CASE and    SOME lower                          CC        ----------------------------------------------------------------- All Upper Case and      Some lower                          Cc

User · Answer

If you can enable the CLR in SQL Server  requires 2005 or later  then you could create a CLR function that uses the TextInfo ToTitleCase built-in function which would allow you to create a culture-aware way of doing this in only a few lines of code

User · Answer

UPDATE titles   SET title         UPPER LEFT title  1             LOWER RIGHT title  LEN title  - 1     http   sqlmag com t-sql how-title-case-column-value

User · Answer

Borrowed and improved on  Richard Sayakanit answer  This handles multiple words  Like his answer  this doesn t use any UDFs  only built-in functions  STRING SPLIT and STRING AGG  and it s pretty fast  STRING AGG requires SQL Server 2017 but you can always use the STUFF XML trick  Won t handle every exception but can work great for many requirements    SELECT StateName    North Carolina   INTO  States UNION ALL SELECT  Texas     WITH cteData AS        SELECT          UPPER LEFT value  1                 LOWER RIGHT value  LEN value  - 1   value  op StateName     FROM    States op     CROSS APPLY STRING SPLIT op StateName       AS ss   SELECT      STRING AGG value       FROM cteData c  GROUP BY StateName

User · Answer

Here is a version that uses a sequence or numbers table rather than a loop   You can modify the WHERE clause to suite your personal rules for when to convert a character to upper case   I have just included a simple set that will upper case any letter that is proceeded by a non-letter with the exception of apostrophes   This does how ever mean that 123apple would have a match on the  a  because  3  is not a letter   If you want just white-space  space  tab  carriage-return  line-feed   you can replace the pattern    a-z   with       Char 32    Char 9    Char 13    Char 10            CREATE FUNCTION String InitCap   string nvarchar 4000    RETURNS nvarchar 4000  AS BEGIN  -- 1  Convert all letters to lower case     DECLARE  InitCap nvarchar 4000   SET  InitCap   Lower  string    -- 2  Using a Sequence  replace the letters that should be upper case with their upper case version     SELECT  InitCap   Stuff   InitCap  n  1  Upper  SubString   InitCap  n  1           FROM           SELECT  1   n1 n   n10 n   n100 n   n1000 n  AS n         FROM        SELECT 0 AS n UNION SELECT    1 UNION SELECT    2 UNION SELECT    3 UNION SELECT    4 UNION SELECT    5 UNION SELECT    6 UNION SELECT    7 UNION SELECT    8 UNION SELECT    9  AS    n1         CROSS JOIN  SELECT 0 AS n UNION SELECT   10 UNION SELECT   20 UNION SELECT   30 UNION SELECT   40 UNION SELECT   50 UNION SELECT   60 UNION SELECT   70 UNION SELECT   80 UNION SELECT   90  AS   n10         CROSS JOIN  SELECT 0 AS n UNION SELECT  100 UNION SELECT  200 UNION SELECT  300 UNION SELECT  400 UNION SELECT  500 UNION SELECT  600 UNION SELECT  700 UNION SELECT  800 UNION SELECT  900  AS  n100         CROSS JOIN  SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000                                                                                                              AS n1000           AS Sequence     WHERE          n BETWEEN 1 AND Len   InitCap       AND SubString   InitCap  n  1   LIKE   a-z                      this character is a letter        AND           n   1                                                       this character is the first  character             OR SubString   InitCap  n-1  1   LIKE    a-z                the previous character is NOT a letter                  AND           n  lt  3                                                       only test the 3rd or greater characters for this exception            OR SubString   InitCap  n-2  3   NOT LIKE   a-z    a-z      exception  The pattern  lt letter gt   lt letter gt  should not capatolize the letter following the apostrophy               -- 3  Return the modified version of the input     RETURN  InitCap  END

User · Answer

I think you will find that the following is more efficient   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str     SET  str   REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE   str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z       RETURN RIGHT  str  LEN  str  - 1  END GO   The replace statement could be cut and pasted directly into a SQL query   It is ultra ugly  however by replacing  str with the column you are interested in  you will not pay a price for an implicit cursor like you will with the udfs thus posted   I find that even using my UDF it is much more efficient   Oh and instead of generating the replace statement by hand use this   -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000  SET  x   0 SET  sql     str  -- actual variable column you want to replace WHILE  x  lt  26 BEGIN     SET  c   CHAR ASCII  a      x      SET  sql    REPLACE      sql              c               UPPER  c              SET  x    x   1 END PRINT  sql   Anyway it depends on the number of rows   I wish  you could just do s  b  a-z   uc  1   but oh well we work with the tools we have   NOTE you would have to use this as you would have to use it as    SELECT dbo ProperCase LOWER column   since the column is in uppercase   It actually works pretty fast on my table of 5 000 entries  not even one second  even with the lower   In response to the flurry of comments regarding internationalization I present the following implementation that handles every ascii character relying only on SQL Server s Implementation of upper and lower   Remember  the variables we are using here are VARCHAR which means that they can only hold ASCII values   In order to use further international alphabets  you have to use NVARCHAR   The logic would be similar but you would need to use UNICODE and NCHAR in place of ASCII AND CHAR and the replace statement would be much more huge      -- Code Generator for expression DECLARE  x  INT       c  CHAR 1        sql    VARCHAR 8000        count  INT SEt  x   0 SET  count   0 SET  sql     str  -- actual variable you want to replace WHILE  x  lt  256 BEGIN     SET  c   CHAR  x      -- Only generate replacement expression for characters where upper and lowercase differ     IF  x   ASCII LOWER  c   AND  x    ASCII UPPER  c       BEGIN         SET  sql    REPLACE      sql              c               UPPER  c                  SET  count    count   1     END     SET  x    x   1 END PRINT  sql PRINT  Total characters substituted      CONVERT VARCHAR 255    count    Basically the premise of the my method is trading pre-computing for efficiency   The full ASCII implementation is as follows   IF OBJECT ID  dbo ProperCase   IS NOT NULL     DROP FUNCTION dbo ProperCase GO CREATE FUNCTION dbo PROPERCASE        str VARCHAR 8000   RETURNS VARCHAR 8000  AS BEGIN     SET  str          str SET  str       REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE  str    a     A      b     B      c     C      d     D      e     E      f     F      g     G      h     H      i     I      j     J      k     K      l     L      m     M      n     N      o     O      p     P      q     Q      r     R      s     S      t     T      u     U      v     V      w     W      x     X      y     Y      z     Z                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     RETURN RIGHT  str  LEN  str  - 1  END GO

User · Answer

I know this is late post in this thread but  worth looking  This function works for me ever time  So thought of sharing it   CREATE FUNCTION  dbo   fnConvert TitleCase    InputString VARCHAR 4000    RETURNS VARCHAR 4000  AS BEGIN DECLARE  Index INT DECLARE  Char CHAR 1  DECLARE  OutputString VARCHAR 255   SET  OutputString   LOWER  InputString  SET  Index   2 SET  OutputString   STUFF  OutputString  1  1 UPPER SUBSTRING  InputString 1 1     WHILE  Index  lt   LEN  InputString  BEGIN     SET  Char   SUBSTRING  InputString   Index  1      IF  Char IN                                           -          amp                 IF  Index   1  lt   LEN  InputString  BEGIN     IF  Char             OR     UPPER SUBSTRING  InputString   Index   1  1       S      SET  OutputString       STUFF  OutputString   Index   1  1 UPPER SUBSTRING  InputString   Index   1  1    END     SET  Index    Index   1 END  RETURN ISNULL  OutputString     END   Test calls   select dbo fnConvert TitleCase Upper                           CC         as test select dbo fnConvert TitleCase upper  Whatever the mind of man can conceive and believe  it can achieve      Napoleon hill    as test   Results

User · Answer

Is it too late to go back and get the un-uppercased data    The von Neumann s  McCain s  DeGuzman s  and the Johnson-Smith s of your client base may not like the result of your processing     Also  I m guessing that this is intended to be a one-time upgrade of the data   It might be easier to export  filter modify  and re-import the corrected names into the db  and then you can use non-SQL approaches to name fixing

User · Answer

Here s a UDF that will do the trick     create function ProperCase  Text as varchar 8000   returns varchar 8000  as begin   declare  Reset bit    declare  Ret varchar 8000     declare  i int    declare  c char 1      if  Text is null     return null     select  Reset   1   i   1   Ret          while   i  lt   len  Text       select  c   substring  Text   i  1          Ret    Ret   case when  Reset   1 then UPPER  c  else LOWER  c  end         Reset   case when  c like   a-zA-Z   then 0 else 1 end         i    i   1   return  Ret end   You will still have to use it to update your data though

User · Answer

On Server Server 2016 and newer  you can use STRING SPLIT  with t as       select  GOOFYEAR Tire and Rubber Company  as n     union all     select  THE HAPPY BEAR  as n     union all     select  MONK HOUSE SALES  as n     union all     select  FORUM COMMUNICATIONS  as n   select     n                select                     upper left value  1                 lower substring value  2  999                     from               select value             from string split t n                 as sq         for xml path            as title cased from t  Example

User · Answer

Copy and paste your data into MS Word and use built in text-conversion to  Capitalize Each Word    Compare against your original data to address exceptions  Can t see any way around manually sidestepping  MacDonald  and  IBM  type exceptions but this was how I got it done FWIW

[sql] SQL Server: Make all UPPER case to Proper Case/Title Case

Examples related to sql

Examples related to sql-server

Examples related to uppercase

Examples related to title-case