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

1 comment so far

  1. Tim Watts on

    Many thanks – just what I was looking for…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: