User Tools

Site Tools


microsoft:microsoft_sql_server:sql:format:capitalize_the_first_letter_of_each_word

Microsoft - Microsoft SQL Server - SQL - Format - Capitalize the first letter of each word

CREATE FUNCTION dbo.InitCap (@inStr VARCHAR(8000))
  RETURNS VARCHAR(8000)
  AS
  BEGIN
    DECLARE @outStr VARCHAR(8000) = LOWER(@inStr),
		 @CHAR CHAR(1),	
		 @alphanum BIT = 0,
		 @len INT = LEN(@inStr),
                 @pos INT = 1;		  
 
    -- Iterate through all characters in the input string
    WHILE @pos <= @len BEGIN
 
      -- Get the next character
      SET @CHAR = SUBSTRING(@inStr, @pos, 1);
 
      -- If the position is first, or the previous characater is not alphanumeric
      -- convert the current character to upper case
      IF @pos = 1 OR @alphanum = 0
        SET @outStr = STUFF(@outStr, @pos, 1, UPPER(@CHAR));
 
      SET @pos = @pos + 1;
 
      -- Define if the current character is non-alphanumeric
      IF ASCII(@CHAR) <= 47 OR (ASCII(@CHAR) BETWEEN 58 AND 64) OR
	  (ASCII(@CHAR) BETWEEN 91 AND 96) OR (ASCII(@CHAR) BETWEEN 123 AND 126)
	  SET @alphanum = 0;
      ELSE
	  SET @alphanum = 1;
 
    END
 
   RETURN @outStr;		   
  END
microsoft/microsoft_sql_server/sql/format/capitalize_the_first_letter_of_each_word.txt · Last modified: 2021/08/05 16:50 by peter

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki