Solved

Problem with title case function

Posted on 2009-07-13
3
156 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:chtru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:chtru
ID: 24845856
This problem just got a lot more urgent!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24846617
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24846630
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question