Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with title case function

Posted on 2009-07-13
3
Medium Priority
?
159 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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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