asked on

Upper case to Pascal case in sql server

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

Imran Javed Zia

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 Zia

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

anillucky31

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

Alpesh Patel

Select upper('Alpesh')

dsk1234

ASKER

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

anillucky31

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

update yourtable set state = dbo.PascalCase(state)

dsk1234

ASKER

I am getting following error

PascalCase' is not a recognized built-in function name when I try to use above function

ASKER CERTIFIED SOLUTION

anillucky31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anillucky31

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

dsk1234

ASKER

So there is no built in function in sql server 2005?

anillucky31

ther is no inbuilt function. you have to create your own user function as given above

dsk1234

ASKER

Hi anillucky31,

That worked, thank you so much for the help.