How to Convert All Uppercase Values to Proper Case in SQL Server?

SQL Server: Transforming Uppercase Data to Proper Case

Managing data imported entirely in uppercase within SQL Server can be cumbersome. This article provides a solution for efficiently converting such data to proper case.

The following function provides a robust method for this conversion:

<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 @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;</code>

This function iterates through the input string, identifying words separated by whitespace. It capitalizes the first letter of each word while preserving the case of subsequent letters.

Example Usage:

<code class="language-sql">SELECT dbo.ToProperCase('ALL UPPER CASE and    SOME lower ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ');
-- Result: All Upper Case and      Some lower Ää Öö Üü Éé Øø Cc Ææ</code>

This script offers a streamlined approach to converting uppercase data to proper case, thereby improving data readability and consistency within your SQL Server database.

The above is the detailed content of How to Convert All Uppercase Values to Proper Case in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!