Upper case to Pascal case in sql server

dsk1234
dsk1234 used Ask the Experts™
on
I have a requirement to change existing all state names (with capital letters ie NEW YORK)

to pascal cases  (first letter capitalized in each word ie New York).

How can I achieve this using sql server 2005
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
Please try following

CREATE FUNCTION [dbo].[PascalCase] (@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN  
DECLARE @Result varchar(2000)  
SET @Str = LOWER(@Str) + ' '  
SET @Result = ''  
WHILE 1=1  
BEGIN    
IF PATINDEX('% %',@Str) = 0
BREAK    
SET @Result = @Result + UPPER(Left(@Str,1))+     SubString  (@Str,2,CharIndex(' ',@Str)-1)    
SET @Str = SubString(@Str,       CharIndex(' ',@Str)+1,Len(@Str))  
END  
SET @Result = Left(@Result,Len(@Result))  
RETURN @Result
END
Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
or try this

create function PascalCase(@PascalName varchar(max))
returns varchar(max) as
begin      declare @char char(1)    
set @char = 'A'      
-- Loop through the letters A - Z, replace them with a space and the letter    
while ascii(@char) <= ascii('Z')    
begin        
set @PascalName = replace(@PascalName, @char collate Latin1_General_CS_AS, ' ' + @char)          
set @char = char(ascii(@char) + 1)    
end      
return LTRIM(@PascalName)
--remove extra space at the beginning  
end
User this function

create  FUNCTION dbo.ufn_PascalCase(@str AS VARCHAR(MAX)) RETURNS VARCHAR(MAX)
BEGIN
    SET @str = LOWER(@str)

    DECLARE @result VARCHAR(MAX)  
   
    SET @result = ''
   

    DECLARE @spaceIndex INT
     SET @spaceIndex = CHARINDEX(' ', @str)
    WHILE @spaceIndex > 0
    BEGIN
        SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, @spaceIndex - 1)
        SET @str = SUBSTRING(@str, @spaceIndex + 1, len(@str))
        SET @spaceIndex = CHARINDEX(' ', @str)
    END

    SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, len(@str))

    RETURN @result
END
create  FUNCTION dbo.PascalCase(@str AS VARCHAR(MAX)) RETURNS VARCHAR(MAX)
BEGIN
    SET @str = LOWER(@str)

    DECLARE @result VARCHAR(MAX)  
    
    SET @result = ''
    

    DECLARE @spaceIndex INT
     SET @spaceIndex = CHARINDEX(' ', @str)
    WHILE @spaceIndex > 0
    BEGIN
        SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, @spaceIndex - 1)
        SET @str = SUBSTRING(@str, @spaceIndex + 1, len(@str))
        SET @spaceIndex = CHARINDEX(' ', @str)
    END

    SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, len(@str))

    RETURN @result
END

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Select upper('Alpesh')

Author

Commented:
i just want to update existing states column data (Capitalized) to Pascal case in a table.

use this statement  after creating the function PascalCase i my previous post


update yourtable set state = dbo.PascalCase(state)

Author

Commented:
I am getting following error

PascalCase' is not a recognized built-in function name when I try to use above function
first create this function in you database

create  FUNCTION dbo.PascalCase(@str AS VARCHAR(MAX)) RETURNS VARCHAR(MAX)
BEGIN
    SET @str = LOWER(@str)

    DECLARE @result VARCHAR(MAX)  
   
    SET @result = ''
   

    DECLARE @spaceIndex INT
     SET @spaceIndex = CHARINDEX(' ', @str)
    WHILE @spaceIndex > 0
    BEGIN
        SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, @spaceIndex - 1)
        SET @str = SUBSTRING(@str, @spaceIndex + 1, len(@str))
        SET @spaceIndex = CHARINDEX(' ', @str)
    END

    SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, len(@str))

    RETURN @result
END


then use this function, i am assuming the dbo is database owner for this.

update yourtable set state = dbo.PascalCase(state)


create  FUNCTION dbo.PascalCase(@str AS VARCHAR(MAX)) RETURNS VARCHAR(MAX)
BEGIN
    SET @str = LOWER(@str)

    DECLARE @result VARCHAR(MAX)  
    
    SET @result = ''
    

    DECLARE @spaceIndex INT
     SET @spaceIndex = CHARINDEX(' ', @str)
    WHILE @spaceIndex > 0
    BEGIN
        SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, @spaceIndex - 1)
        SET @str = SUBSTRING(@str, @spaceIndex + 1, len(@str))
        SET @spaceIndex = CHARINDEX(' ', @str)
    END

    SET @result = @result + UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, len(@str))

    RETURN @result
END

Open in new window

do not forgot dbo. in calling the function otherwise you will see the same error.

Author

Commented:
So there is no built in function in sql server 2005?
ther is no inbuilt function. you have to create your own user function as given above

Author

Commented:
Hi anillucky31,

That worked, thank you so much for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial