troubleshooting Question

SQL FUNCTION TO Capitalize Each Word in a String

Avatar of josephdts
josephdts asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
10 Comments2 Solutions1123 ViewsLast Modified:
The following function capitalizes each word in a string. How do I make it accept accented characters?
E.G
select dbo.InitialCap('MY TEST')  gives result 'My Test'
How do I make it accept accented characters like 'É'? For now,  select dbo.InitialCap('MY TÉST') returns My TÉSt
The code is below:
ALTER FUNCTION [dbo].[InitialCap](@String VARCHAR(8000))
                  RETURNS VARCHAR(8000)
                 AS
 BEGIN

                   DECLARE @Position INT;

SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
                    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);

                    WHILE @Position > 0
                    SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
                    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);

                     RETURN @String;
  END ;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros