Simple T-SQL Proper Case User-Defined Function

Today I found a nice ol’ post about Simple T-SQL Proper Case User-Defined Function

What this function does, it takes a string and makes the words a proper case.

Here is the script:

 

ALTER FUNCTION [dbo].[f_ProperCase] ( @Text AS VARCHAR(512) )
RETURNS VARCHAR(512)
AS 
    BEGIN
        DECLARE @Reset BIT
        DECLARE @Ret VARCHAR(512)
        DECLARE @i INT
        DECLARE @c CHAR(1)
        SELECT  @Reset = 1 ,
                @i = 1 ,
                @Ret = ''
        WHILE @i <= LEN(@Text) 
            SELECT  @c = SUBSTRING(@Text, @i, 1) ,
                    @Ret = @Ret + CASE WHEN @Reset = 1 THEN UPPER(@c)
                                       ELSE LOWER(@c)
                                  END ,
                    @Reset = CASE WHEN CASE WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-z] [DOL]'''
                                            THEN 1
                                            WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-z] [D][I]'
                                            THEN 1
                                            WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-z] [M][C]'
                                            THEN 1
                                            ELSE 0
                                       END = 1 THEN 1
                                  ELSE CASE WHEN @c LIKE '[a-zA-Z]'
                                                 OR @c IN ( '''' ) THEN 0
                                            ELSE 1
                                       END
                             END ,
                    @i = @i + 1
 
        RETURN @Ret 
    END
 
-- Test: SELECT dbo.f_ProperCase('it''s crazy! öre, spö i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )

But there is a problem with this. I realized that in the regular expression is specified that ‘z’ is the last letter of the alphabet. But this is not always the case. :)

For example, in Swedish, there are 3 more letters after ‘z’, and this makes the last one being ‘ö’.

So for the purpose of making the proper case of Swedish phrases, the script should look like this:
 

ALTER FUNCTION [dbo].[f_ProperCase] ( @Text AS VARCHAR(512) )
RETURNS VARCHAR(512)
AS 
    BEGIN
        DECLARE @Reset BIT
        DECLARE @Ret VARCHAR(512)
        DECLARE @i INT
        DECLARE @c CHAR(1)
        SELECT  @Reset = 1 ,
                @i = 1 ,
                @Ret = ''
        WHILE @i <= LEN(@Text) 
            SELECT  @c = SUBSTRING(@Text, @i, 1) ,
                    @Ret = @Ret + CASE WHEN @Reset = 1 THEN UPPER(@c)
                                       ELSE LOWER(@c)
                                  END ,
                    @Reset = CASE WHEN CASE WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-ö] [DOL]'''
                                            THEN 1
                                            WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-ö] [D][I]'
                                            THEN 1
                                            WHEN SUBSTRING(@Text, @i - 4, 5) LIKE '_[a-ö] [M][C]'
                                            THEN 1
                                            ELSE 0
                                       END = 1 THEN 1
                                  ELSE CASE WHEN @c LIKE '[a-öA-Ö]'
                                                 OR @c IN ( '''' ) THEN 0
                                            ELSE 1
                                       END
                             END ,
                    @i = @i + 1
 
        RETURN @Ret 
    END
 
-- Test: SELECT dbo.f_ProperCase('it''s crazy! öre, spö i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )

So, it really turns out that ‘z’ is not the last letter of the alphabet. :)
 

 

Comments are closed.