chtru
asked on
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER