How Can I Convert All Uppercase Text to Proper Case in SQL Server While Handling Non-English Characters and Whitespace Variations?

Advanced SQL Server Function for Proper Case Conversion

This article details a robust SQL Server function designed to convert uppercase text to proper case while addressing complexities such as non-English characters and varying whitespace. The function offers several key advantages:

• Handles Whitespace Variations: Correctly handles multiple spaces and various whitespace characters (including line breaks, tabs, and non-breaking spaces).
• Supports Non-English Alphabets: Accurately processes text containing characters from alphabets beyond English.
• Maintains Lowercase Words: Preserves any existing lowercase words within the input string.
• Version Compatibility: Functions across different SQL Server versions.
• Customizable Whitespace: The whitespace definition is configurable, allowing for tailored handling.

Function Implementation:

<code class="language-sql">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 Character
  DECLARE @f INT;           -- First Letter Flag (1/0)
  DECLARE @o VARCHAR(255);  -- Output String
  DECLARE @w VARCHAR(10);   -- Whitespace Characters

  SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + ']';
  SET @i = 1;
  SET @l = LEN(@string);
  SET @f = 1;
  SET @o = '';

  WHILE @i <= @l
  BEGIN
    SET @c = SUBSTRING(@string, @i, 1);
    IF PATINDEX(@w, @c) > 0
    BEGIN
      SET @f = 1;
      SET @o = @o + @c;
    END
    ELSE
    BEGIN
      IF @f = 1
      BEGIN
        SET @o = @o + UPPER(@c);
        SET @f = 0;
      END
      ELSE
        SET @o = @o + LOWER(@c);
    END
    SET @i = @i + 1;
  END
  RETURN @o;
END;</code>

Usage Example:

<code class="language-sql">SELECT dbo.ToProperCase('ALL UPPER CASE and    SOME lower ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ');</code>

Example Output:

All Upper Case and Some lower Ää Öö Üü Éé Øø Cc Ææ

The above is the detailed content of How Can I Convert All Uppercase Text to Proper Case in SQL Server While Handling Non-English Characters and Whitespace Variations?. For more information, please follow other related articles on the PHP Chinese website!