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

by

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 <= @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 ÄÄ ÖÖ ÜÜ ÉÉ ØØ ĈĈ ÆÆ')
-----------------------------------------------------------------
All Upper Case and      Some lower Ää Öö Üü Éé Øø Cc Ææ

More Related Contents:

  1. Get Parent with multiple children in desc order
  2. How can I schedule a job to run a SQL query daily?
  3. Convert Month Number to Month Name Function in SQL
  4. Reset identity seed after deleting records in SQL Server
  5. How to avoid the “divide by zero” error in SQL?
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Removing leading zeroes from a field in a SQL statement
  8. How to simulate UNPIVOT in Access?
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. Better techniques for trimming leading zeros in SQL Server?
  11. How to print VARCHAR(MAX) using Print Statement?
  12. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  13. Query error with ambiguous column name in SQL
  14. Why (and how) to split column using master..spt_values?
  15. How would you implement sequences in Microsoft SQL Server?
  16. What does =* mean?
  17. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  18. SSIS Package not wanting to fetch metadata of temporary table
  19. SQL GROUP BY CASE statement with aggregate function
  20. How can I include null values in a MIN or MAX?
  21. Favourite performance tuning tricks [closed]
  22. Query a database based on result of query from another database
  23. How to delete the top 1000 rows from a table using Sql Server 2008?
  24. CHECK CONSTRAINT on multiple columns
  25. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  26. Inner Joining three tables
  27. How to return default value from SQL query
  28. Group consecutive rows of same value using time spans
  29. Conditional aggregation performance
  30. SQL add filter only if a variable is not null

Leave a Comment