locked
How to Capitalize One or More Words RRS feed

  • Question

  • What is the best way to get Philadelphia and New York City in the following sample:

    DECLARE @Word nvarchar(32) = 'philadeLphia'
    DECLARE @Phrase nvarchar(32) = 'new yorK city'
    Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, November 21, 2010 7:40 AM

Answers

  • Take a look at this function here

    Proper case T-SQL function


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Friday, December 3, 2010 12:07 AM
    Sunday, November 21, 2010 8:13 PM
  • In UDF form:

    CREATE FUNCTION dbo.fn_Capitalize01
    (
    	@str Varchar(100)
    ) RETURNS Varchar(100)
    AS
    BEGIN
    	DECLARE @i INTEGER
    	SET @i=1
    	WHILE @i <= LEN(@str) BEGIN
    		SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
    		SET @i = @i + 1
    	END
    	RETURN @str
    END
    GO
    
    CREATE FUNCTION dbo.fn_Capitalize02
    (
    	@str Varchar(100)
    ) RETURNS Varchar(100)
    AS
    BEGIN
    	DECLARE @res Varchar(100)
    	DECLARE @i INTEGER
    	DECLARE @c Char
    	DECLARE @cap Bit
    	SET @i=1
    	SET @cap=1
    	SET @res=''
    
    	WHILE @i <= LEN(@str) BEGIN
    		SET @c = LOWER(SUBSTRING(@str,@i,1))
    		IF @cap=1
    			SET @c = UPPER(@c)
    		IF @c = ' ' 
    			SET @cap=1
    		ELSE
    			SET @cap=0
    		SET @res = @res + @c
    		SET @i = @i + 1
    	END
    	RETURN @res
    END
    GO
    
    


    Michael Asher
    • Marked as answer by Kalman Toth Friday, December 3, 2010 12:08 AM
    Wednesday, November 24, 2010 9:58 PM
  • You know what I think is the funniest part about all of this? At the time I wrote that function, I was eyebrow deep in to collations and on a lark, decided to see how the PatIndex function worked with different collations.  Whenever I write code, I always keep performance in mind.  In this case, I wasn't specifically trying to make it the fastest performing.  I was primarily concerned with accuracy, coupled with a clever method for reducing the number of loops.
    • Marked as answer by Kalman Toth Friday, December 3, 2010 4:44 AM
    Friday, December 3, 2010 12:12 AM

All replies

  • A bit clumsy, but it does the trick:

     

    SET NOCOUNT ON
    
    DECLARE @str Varchar(100)
    DECLARE @res Varchar(100)
    DECLARE @i INTEGER
    DECLARE @c Char
    DECLARE @cap Bit
    
    SET @str = 'new yOrK cItY'
    
    SET @i=1
    SET @cap=1
    SET @res=''
    
    WHILE @i <= LEN(@str) BEGIN
    	SET @c = LOWER(SUBSTRING(@str,@i,1))
    	IF @cap=1
    		SET @c = UPPER(@c)
    	IF @c = ' ' 
    		SET @cap=1
    	ELSE
    		SET @cap=0
    	SET @res = @res + @c
    	SET @i = @i + 1
    END
    
    PRINT @res


    Michael Asher
    Sunday, November 21, 2010 7:53 AM
  • A more succinct version:

     

    DECLARE @str Varchar(100)
    DECLARE @i INTEGER
    
    SET @str = 'nEw yOrK cItY'
    SET @i=1
    
    WHILE @i <= LEN(@str) BEGIN
    	SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
    	SET @i = @i + 1
    END
    
    PRINT @str
    


    Michael Asher
    Sunday, November 21, 2010 8:03 AM
  • My two cents

    CREATE FUNCTION dbo.fn_capitalize
    (
    @str AS nvarchar(100)
    )
    RETURNS nvarchar(100)
    AS
    BEGIN

    DECLARE
    @ret_str AS varchar(100),
    @pos AS int,
    @len AS int

    SELECT
    @ret_str = N' ' + LOWER(@str),
    @pos = 1,
    @len = LEN(@str) + 1

    WHILE @pos > 0 AND @pos < @len
    BEGIN
    SET @ret_str = STUFF(@ret_str,
    @pos + 1,
    1,
    UPPER(SUBSTRING(@ret_str,@pos + 1, 1)))
    SET @pos = CHARINDEX(N' ', @ret_str, @pos + 1)
    END

    RETURN RIGHT(@ret_str, @len - 1)

    END

    ------
    SELECT dbo.fn_capitalize('new yorK city')


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 21, 2010 8:44 AM
    Answerer
  • There is no better way. We should write some kind of loopy code. But By using Regular Expression,I think we can replace the first character after every space with its upper character. But as RegEx is not much supported in t-sql, Probably a CLR function will perform more better here...
    Sunday, November 21, 2010 8:53 AM
  • Is bitwise operation a possibility (for one English word)?
    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:55 AM
    Sunday, November 21, 2010 12:21 PM
  • Take a look at this function here

    Proper case T-SQL function


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Friday, December 3, 2010 12:07 AM
    Sunday, November 21, 2010 8:13 PM
  • my version.

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringTitleCase]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[TitleCase]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[TitleCase]
    (@Input NVARCHAR(4000))
    RETURNS TABLE
    AS
    RETURN
    SELECT 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (REPLACE(STUFF(@Input,1,1,UPPER(SUBSTRING(@Input,1,1)))
    ,' a',' A'),' b',' B'),' c',' C')
    ,' d',' D'),' e',' E'),' f',' F'),' g',' G')
    ,' h',' H'),' i',' I'),' j',' J'),' k',' K')
    ,' l',' L'),' m',' M'),' n',' N'),' o',' O')
    ,' p',' P'),' q',' Q'),' r',' R'),' s',' S')
    ,' t',' T'),' u',' U'),' v',' V'),' w',' W')
    ,' x',' X'),' y',' Y'),' z',' Z') [Output]
    
    GO
    

    Jon
    Monday, November 22, 2010 9:01 AM
  • Hello an other version

     

    DECLARE @Word nvarchar(32) = 'new yorK city'
    DECLARE @Word2 nvarchar(32) = @Word
    
    select @Word = STUFF(LOWER(@Word),1,1,upper(left(@word,1)))
    
    declare @i int, @j int
    set @i = CHARINDEX(' ',@Word2,1)
    
    while @i > 0
    begin
    	select @j = @i, @i = @i + 1
    	select @Word = STUFF(@word,@i,1,upper(SUBSTRING(@word,@i,1)))
    	
    	select @Word2 = STUFF(@word2,@j,1,'-')
    
    	select @i = CHARINDEX(' ',@Word2,1)
    end
    
    select @Word
    

    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Monday, November 22, 2010 9:22 AM
  • Superb collection! Will start testing soon....
    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:56 AM
    Monday, November 22, 2010 7:04 PM
  • MY MISTAKE: I need the solution as scalar-valued UDF. I would appreciate if you update your post. Thanks.

     

    Uri's UDF tested 50 msec on Production.Product table (504) rows:

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_uri(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- Uri 50 msec

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:56 AM
    Tuesday, November 23, 2010 4:11 PM
  • Have you tested George Mastros solution? I believe Brad Schulz also has a version in his

    Handy String Functions
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 23, 2010 4:12 PM
  • Have you tested George Mastros solution?

    Lightning fast! 6 msec.

    Naomi's post ( http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function ).

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_mastros(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- Mastros 6 msec
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:56 AM
    Tuesday, November 23, 2010 4:20 PM
  • --Date Created: Nov-23-2010
    --Author: Pratyaharam
    --Email: v.bmadhavan@hotmail.com
    --Purpose : To convert each word of a sentence to proper case 
    BEGIN
    DECLARE @str VARCHAR(8000)
    SELECT @str='any senteNce herE with mixed cAsEs'
    DECLARE @arr table ( ident int IDENTITY,c CHAR(1))
    WHILE LEN(@str) > 0
     BEGIN 
     INSERT INTO @arr 
    	 SELECT LEFT(@str,1) 
     SET @str=CASE WHEN Left(@str,1) =' ' 
    	THEN UPPER(SUBSTRING(@str,2,LEN(@str)-1) ) 
    		ELSE LOWER(SUBSTRING(@str,2,LEN(@str)-1) ) END;
    END
    UPDATE @arr set c=UPPER(c) where ident=1
    SET @str=''
    SELECT @str=@str+c FROM @arr
    SELECT @str 
    END
    Tuesday, November 23, 2010 6:23 PM
  • --Limitation : No duplicate words in the sentence allowed
    --Email: v.bmadhavan@hotmail.com
    --Name: Pratyaharam
    --Date: 11-23-2010
    --Purpose: to Convert a senternce to proper case ( Limitation : No duplicate words)
    IF EXISTS (SELECT * FROM sysobjects WHERE xtype='FN' AND name='MakeProperCase')
    BEGIN
    	DROP FUNCTION dbo.MakeProperCase
    END
    GO
    CREATE FUNCTION dbo.MakeProperCase(@STR VARCHAR(8000))
    RETURNS VARCHAR(8000) AS
    BEGIN
    DECLARE @POS int,@w VARCHAR(100),@result VARCHAR(8000)
    SET @result=''
    WHILE LEN(@str)>0
    BEGIN
    	SET @pos = charindex(' ',REVERSE(@str),1)
    	IF @POS = 0 break
    	SELECT @w=ltrim(rtrim(reverse(LEFT(reverse(@str),@POS))))
    	SELECT @w=LEFT(upper(@w),1)+SUBSTRING(LOWER(@w),2,LEN(@w))
    	select @result = @w +' '+ @result
    	select @str=ltrim(rtrim(reverse(REPLACE(reverse(@str),reverse(@w),''))))
    END
    
    RETURN LEFT(upper(@str),1)+SUBSTRING(LOWER(@str),2,LEN(@str)) +' '+ @result
    END --function
    GO
    SELECT dbo.MakeProperCase('any sentEnce wiTh woRds will be turNed to proper case')
    SELECT dbo.MakeProperCase('but no duplicates allowed in this version')
    
    
    
    
    
    Tuesday, November 23, 2010 7:39 PM
  • What is the best way to get Philadelphia and New York City in the following sample:

    DECLARE @Word nvarchar(32) = 'philadeLphia'
    
    DECLARE @Phrase nvarchar(32) = 'new yorK city'
    
    
    Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    Please define your criteria for judging what is "best".

    Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?

    Tuesday, November 23, 2010 7:42 PM
  • Have you tested George Mastros solution?

    Lightning fast! 6 msec.

    Naomi's post ( http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function ).

    DECLARE @StartTime datetime
    
    
    
    DBCC DROPCLEANBUFFERS 
    
    
    
    SET @StartTime = GETDATE()
    
    
    
    SELECT dbo.fn_capitalize_mastros(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    
    
    -- Mastros 6 msec
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    doesnt that table you are testing with have less than 1000 rows in it?

     

     

    CREATE FUNCTION dbo.fn_capitalize
    (
    @str AS nvarchar(4000)
    )
    RETURNS nvarchar(4000)
    AS
    BEGIN
    	RETURN 
    	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    	(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    	(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    	(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    	(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    	(REPLACE(STUFF(@str,1,1,UPPER(SUBSTRING(@str,1,1)))
    	,' a',' A'),' b',' B'),' c',' C')
    	,' d',' D'),' e',' E'),' f',' F'),' g',' G')
    	,' h',' H'),' i',' I'),' j',' J'),' k',' K')
    	,' l',' L'),' m',' M'),' n',' N'),' o',' O')
    	,' p',' P'),' q',' Q'),' r',' R'),' s',' S')
    	,' t',' T'),' u',' U'),' v',' V'),' w',' W')
    	,' x',' X'),' y',' Y'),' z',' Z') 
    END
    

    Jon
    Tuesday, November 23, 2010 8:02 PM
  • Please define your criteria for judging what is "best".

    Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?


    Best: best performing.  Sorry, SS only.
    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:56 AM
    Tuesday, November 23, 2010 8:12 PM
  • Thanks Jon - results (504 rows):

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS  
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_gurgul(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- Gurgul 70 msec
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:57 AM
    Tuesday, November 23, 2010 8:15 PM
  • --Limitation : No duplicate words in the sentence allowed
    
    
    Sorry, that is disqualification. I have no control over that.
    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:57 AM
    Tuesday, November 23, 2010 8:17 PM
  • note that the simple approach i posted is going to fall behind quickly with longer strings and multiple spaces.

     


    Jon
    Tuesday, November 23, 2010 8:25 PM
  • Please define your criteria for judging what is "best".

    Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?


    Best: best performing.  Sorry, SS only.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    Really, what I was getting at was some of the anomolies noted in George's blog. Specifically, names such as:

    • McDonald
    • O'Hanion
    • DeZego
    • last names such as Bush-Clinton

    I assume I don't have to worry about lower casing words such as "of"

    I also assume that we ignore lower casing people that purposely start one or more of their names with a lower case letter and they get upper case names; If a persons name is (sic) james Martin he still gets James Martin

    Tuesday, November 23, 2010 8:45 PM
  • Bush-Clinton? Obama-Bush? james Martin?

    Sympathy only, speed rules.

     


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:59 AM
    Tuesday, November 23, 2010 9:18 PM
  • Based on George's blog, but I am not sure how well it performs and I cannot tell whether or not it is "right":

    alter function dbo.Proper(@string varchar(8000))
    returns varchar(8000)
    with returns null on null input
    as
    
    begin
    
     declare @loc integer
    
     select 
      @string = stuff(lower(@string), 1, 1, upper(left(@string,1))),
      @loc = patindex('%[-/ ][a-z]%', @string)
    
     if @loc = 0
       return @string
    
    -- ;WITH
    --  L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    --  L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
    --  L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
    --  L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
    --  L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
    -- Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
     select
      @string = stuff(@string, @loc, 2, upper(substring(@string, @loc, 2))), 
      @loc = @loc + patindex('%[-/ ][a-z]%', substring(@string, @loc+1, 8000))
     from numbers
     where n <= 8000
      and n <= len(@string) 
            - len(replace(replace(replace(@string,' ',''),'-',''),'/',''))
      and @loc > 0
    
     return @string
    
    end
    
    go
    
    
    Wednesday, November 24, 2010 2:34 PM
  • Thanks Kent. I am afraid quite sluggish.

    A very useful segment from your code:

    -- T-SQL Create a number table
    ;WITH
    	L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    	L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
    	L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
    	L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
    	L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
    	Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
    SELECT * into Numbers FROM Numbers
    -- (65536 row(s) affected)
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:57 AM
    Wednesday, November 24, 2010 4:53 PM
  • And how did my solution fare?
    Michael Asher
    • Proposed as answer by pratyaharam Wednesday, November 24, 2010 6:45 PM
    • Unproposed as answer by Kalman Toth Wednesday, November 24, 2010 7:28 PM
    Wednesday, November 24, 2010 5:52 PM
  • --Function to convert a string to proper case
    --Date: 24-Nov-2010
    --E-mail: v.bmadhavan@hotmail.com
    --Limitation: 32 characters long string
    -- This version is using recursion so has limitations due to limited nesting
    if exists (select * from information_schema.ROUTINES 
    	where ROUTINE_NAME='PROPERCASE' and ROUTINE_TYPE='FUNCTION')
    drop function dbo.propercase
    go
    create function dbo.propercase
    (
    	@str varchar(8000),
    	@pos int =1 ,
    	@up int =1
    )
    returns varchar(8000) as
    begin
    declare @w char(1)
     select @w=substring(@str,@pos,1)
     select @w = case when @up = 1 then upper(@w)
    				 when @up = 0 then lower(@w)
    				 end;
    return 
    		case 
    			when @pos = len(@str) then @w
    			when @pos < len(@str) and @w<>' ' then @w + dbo.propercase(@str,@pos+1,0)		
    			when @pos < len(@str) and @w=' ' then @w + dbo.propercase(@str,@pos+1,1)
    		else ''
    		end;
    
    end
    go
    
    select dbo.propercase('nEw yOrK cItY',default,default);
    
    
    
    
    • Edited by pratyaharam Friday, November 26, 2010 6:19 PM
    Wednesday, November 24, 2010 6:49 PM
  • And how did my solution fare?

     

    If your solution is a script, can you change it into scalar-valued UDF? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training;Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:57 AM
    Wednesday, November 24, 2010 7:21 PM
  • Result for pratyaharam  66 msec.

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_pratyaharam(lower(Name),1,1) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- pratyaharam 66 msec
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:58 AM
    Wednesday, November 24, 2010 7:27 PM
  • In UDF form:

    CREATE FUNCTION dbo.fn_Capitalize01
    (
    	@str Varchar(100)
    ) RETURNS Varchar(100)
    AS
    BEGIN
    	DECLARE @i INTEGER
    	SET @i=1
    	WHILE @i <= LEN(@str) BEGIN
    		SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
    		SET @i = @i + 1
    	END
    	RETURN @str
    END
    GO
    
    CREATE FUNCTION dbo.fn_Capitalize02
    (
    	@str Varchar(100)
    ) RETURNS Varchar(100)
    AS
    BEGIN
    	DECLARE @res Varchar(100)
    	DECLARE @i INTEGER
    	DECLARE @c Char
    	DECLARE @cap Bit
    	SET @i=1
    	SET @cap=1
    	SET @res=''
    
    	WHILE @i <= LEN(@str) BEGIN
    		SET @c = LOWER(SUBSTRING(@str,@i,1))
    		IF @cap=1
    			SET @c = UPPER(@c)
    		IF @c = ' ' 
    			SET @cap=1
    		ELSE
    			SET @cap=0
    		SET @res = @res + @c
    		SET @i = @i + 1
    	END
    	RETURN @res
    END
    GO
    
    


    Michael Asher
    • Marked as answer by Kalman Toth Friday, December 3, 2010 12:08 AM
    Wednesday, November 24, 2010 9:58 PM
  • Thanks Michael. 15 & 20 msec.

    I retested Mastros, it is 6 msec.

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_asher_01(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- Asher 1 15 msec
    
    
    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_asher_02(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    
    -- Asher 2 20 msec
    
    --------------------------------------------------------------------------------
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:58 AM
    Wednesday, November 24, 2010 11:41 PM
  • --Date Created: Nov-23-2010
    --Author: Pratyaharam
    --Email: v.bmadhavan@hotmail.com
    --Purpose : To convert each word of a sentence to proper case 
    
    --I posted one version this earlier in this thread but not as a UDF
    now converting into UDF and requesting for a retest
    
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
    	WHERE ROUTINE_NAME='fn_capitalize_pratyaharam_revised' and routine_type='FUNCTION')
    	DROP FUNCTION fn_capitalize_pratyaharam_revised
    GO	
    CREATE FUNCTION fn_capitalize_pratyaharam_revised(@str VARCHAR(MAX))
    RETURNS VARCHAR(max)
    BEGIN
    --DECLARE @str VARCHAR(max)
    DECLARE @arr table ( ident int IDENTITY,c CHAR(1))
    WHILE LEN(@str) > 0
     BEGIN 
     INSERT INTO @arr 
    	 SELECT LEFT(@str,1) 
     SET @str=CASE WHEN Left(@str,1) =' ' 
    	THEN UPPER(SUBSTRING(@str,2,LEN(@str)-1) ) 
    		ELSE LOWER(SUBSTRING(@str,2,LEN(@str)-1) ) END;
    END
    UPDATE @arr set c=UPPER(c) where ident=1
    SET @str=''
    SELECT @str=@str+c FROM @arr
    RETURN @str 
    END
    GO
    
    
    Friday, November 26, 2010 6:17 PM
  • This is on the slow side: 350 msec.

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_pratyaharam_revised(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    -- pratyaharam_revised: 350 msec
    
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:58 AM
    Friday, November 26, 2010 7:34 PM
  • Another version

     

    --using another method to convert to propercase
    if object_id('udf_propercase_fast') is not null
    drop function udf_propercase_fast
    go
    create function udf_propercase_fast(@str varchar(8000))
    returns varchar (8000) as
    begin
    declare @upper varchar(8000), @lower varchar(8000), @result varchar(8000), @c1 char(1), @c2 char(1), @index int
    	select @upper=upper(@str),@lower=lower(@str),@result = '',@index = 1
    	select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)
    	select @result = @result + @c1
    while (@index<=len(@str) ) begin
     while (ascii(@c1) <> ascii(@c2)) begin
    		select @index = @index + 1
    		select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)
    		if ascii(@c1)= ascii(@c2) break
     		select @result = @result + @c2 	
     end 
      select @result = @result + @c1
      select @index = @index + 1
    	select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)	
    	select @result = @result + @c1
    end 
    return @result
    end
    go
    select dbo.udf_propercase_fast('any sENtEnce wiLL be cOnVerted tO pRopEr case')
    
    
    
    
    Tuesday, November 30, 2010 5:46 PM
  • This one clocks in 80 msec. Also notice: "Women'S Mountain Shorts,  M"

    DECLARE @StartTime datetime
    
    DBCC DROPCLEANBUFFERS 
    
    SET @StartTime = GETDATE()
    
    SELECT dbo.udf_capitalize_pratyaharam_fast(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    -- pratyaharam_fast: 80 msec
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:58 AM
    Tuesday, November 30, 2010 6:15 PM
  • create function dbo.how_fast_is_this_one(@str varchar(max))
    returns varchar(max) as
    begin
    	declare @rlen int,@i int
    	select @rlen=len(@str),@i=1
    	while (@i <= @rlen)	select @str=substring(@str,2,@rlen-1)+ case when @i=1 or right(@str,1)=' ' then upper(left(@str,1)) else lower(left(@str,1)) end,@i=@i+1
    	return(@str)
    end
    go
    select dbo.how_fast_is_this_one('hEllo hOw fast is this vErsIon of cAPItALIzATION')
    
    
    Thursday, December 2, 2010 5:33 PM
  • This one is 90msec.

    DECLARE @StartTime datetime
    DBCC DROPCLEANBUFFERS 
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_pratyaharam_how_fast_is_this_one(lower(Name)) FROM AdventureWorks2008.Production.Product
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    -- dbo.fn_capitalize_pratyaharam_how_fast_is_this_one 90 msec
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:59 AM
    Thursday, December 2, 2010 8:32 PM
  • Have you tested George Mastros solution? I believe Brad Schulz also has a version in his

    Handy String Functions

    That clocks in 90msec.

     

    DECLARE @StartTime datetime
    DBCC DROPCLEANBUFFERS 
    SET @StartTime = GETDATE()
    
    SELECT dbo.fn_capitalize_brad(lower(Name), 0) FROM AdventureWorks2008.Production.Product
    
    SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
    -- dbo.fn_capitalize_brad 90 msec
    

    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Friday, November 10, 2017 9:59 AM
    Thursday, December 2, 2010 9:43 PM
  • You know what I think is the funniest part about all of this? At the time I wrote that function, I was eyebrow deep in to collations and on a lark, decided to see how the PatIndex function worked with different collations.  Whenever I write code, I always keep performance in mind.  In this case, I wasn't specifically trying to make it the fastest performing.  I was primarily concerned with accuracy, coupled with a clever method for reducing the number of loops.
    • Marked as answer by Kalman Toth Friday, December 3, 2010 4:44 AM
    Friday, December 3, 2010 12:12 AM