Archive for October, 2006|Monthly archive page

T-SQL Proper Case Function

Today one of our clients asked us to fix the letter casing in a particular field of the products table. We had just imported their products table from their old system into ours and the products descriptions were all messed up.

Since there is no buit-in function to do that, I had to do it my own.
I’m posting it here in case someone find it useful. 

CREATE FUNCTION [dbo].[ProperCase] 
( 
    @str VARCHAR(500) 
) 
RETURNS VARCHAR(500) 
AS
BEGIN
    IF @str IS NULL
        RETURN NULL  

    DECLARE @counter INT
    DECLARE @ret VARCHAR(255) 
    DECLARE @goUp BIT
    DECLARE @currentChar CHAR 
     
    SET @counter = 1 
    SET @ret = ''         
    SET @goUp = 1 
 
    WHILE @counter <= LEN(@str) 
    BEGIN 
        SET @currentChar = SUBSTRING(@str, @counter, 1) 
        IF @currentChar IN (' ', '-')                    
            SET @goUp  = 1 
        ELSE 
        BEGIN 
            IF @goUp  = 1 
                SET @currentChar = UPPER(@currentChar) 
            ELSE 
                SET @currentChar = LOWER(@currentChar)  
             
            SET @goUp  = 0 
        END 

        SET @ret = @ret + @currentChar
        SET @counter = @counter + 1 
    END 
    RETURN @ret 
END 

After creating the user-defined function in the database, you can use the following line to test it:

SELECT dbo.ProperCase('GOLD 802.11B WIRELESS CARD')
Advertisements