Problem with title case function

Hi i'm trying to make a function that will title case strings, and also deal with capitalising surnames that have Mc's in them properly, ie McIntosh.

I've tried altering a function i found on the internet to have a case statement within it and it errors, when I use the case outside it works fine... is there an issue with my construction?
BEGIN
 
DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)
 
SET @OutputString = LOWER(@InputString)
SET @Index = 1
 
WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END
 
    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END
 
    SET @Index = @Index + 1
 
END
	CASE
		WHEN @OutputString LIKE 'Mc%'
		THEN ('Mc'+ UPPER(SUBSTRING(@OutputString,3,1))+(RIGHT(@OutputString, LEN(@OutputString)-3)))
	END
	
RETURN @OutputString
 
END
GO

Open in new window

chtruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chtruAuthor Commented:
This problem just got a lot more urgent!
0
RiteshShahCommented:
here you go....

BEGIN
Declare @InputString varchar(255)
set @InputString='mccan' 
DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)
 
SET @OutputString = LOWER(@InputString)
SET @Index = 1
 
WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END
 
    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END
 
    SET @Index = @Index + 1
 
END
 
if @OutputString LIKE 'Mc%'
begin
	set @OutputString=('Mc'+ UPPER(SUBSTRING(@OutputString,3,1))+(RIGHT(@OutputString, LEN(@OutputString)-3)))
end
 
        
print @OutputString
 
END
 
GO

Open in new window

0
RiteshShahCommented:
and if you want to do it with CASE only, no IF than have a look:




BEGIN
Declare @InputString varchar(255)
set @InputString='RITESH' 
DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)
 
SET @OutputString = LOWER(@InputString)
SET @Index = 1
 
WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END
 
    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END
 
    SET @Index = @Index + 1
 
END
 
 
   SELECT @OutputString = CASE
                WHEN @OutputString LIKE 'Mc%'
					THEN ('Mc'+ UPPER(SUBSTRING(@OutputString,3,1))+(RIGHT(@OutputString, LEN(@OutputString)-3)))
                ELSE
					@OutputString
        END
 
        
print @OutputString
 
END
 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.