Proper Case Problem

Question

Post reply

Proper Case Problem

Kicking_Fish0713

SSC Eights!

Points: 896
More actions
March 29, 2010 at 11:26 pm

#103917

Hi,
I've created a function that will set column values into Proper case.Say for example:
FROM this format:
AA BB
To this format:
Aa Bb
My problem is,how can I set/change the values ending with roman numeral? Like,
FROM this format:
AA BB II or AA BB III
TO this format:
Aa Bb II or Aa Bb III
Function script:
create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @C char(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @C= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @C like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

i needed to play with data.

Declare @tbl table ( main_text nvarchar(200), Without_Roman_values nvarchar(100),Roman_values nvarchar(50) )

insert into @tbl (main_text)

select 'BHUVH III'

union

SELECT 'DEXY V'

UNION

SELECT 'JACK TONY VIII'

update @tbl

set Without_Roman_values = reverse(substring(reverse(main_text),charindex(' ', reverse(main_text) ,1), LEN(main_text))),

Roman_values = reverse(substring(reverse(main_text), 1, charindex(' ', reverse(main_text) ,1)))

select *,(dbo.ProperCase(Without_Roman_values)+ roman_values )as OutPut from @tbl

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 2

Great idea, Bhuvnesh, but that won't work when there are no Roman numerals at the end.

JOHN JONES will end up as John JONES.

But you could use your idea to strip off that last 'word' from the end of a name and match it against a table or list of possible values ('I','II','III','IV','V','VI', etc) and only leave it intact if it matches.

Rob Schripsema
Propack, Inc.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Rob Schripsema (3/30/2010)
Great idea, Bhuvnesh, but that won't work when there are no Roman numerals at the end.
JOHN JONES will end up as John JONES.
But you could use your idea to strip off that last 'word' from the end of a name and match it against a table or list of possible values ('I','II','III','IV','V','VI', etc) and only leave it intact if it matches.

yes i had same doubt but couldnt think as u did. i am gree with you that we can do some lookup table comparision for ROMAN values.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 4

It might be a better plan to either store the parts of the names separately (e.g. prefix, suffix) or to do this sort of presentation work on the application responsible for generating the input in the first place. Formatting is rarely best done inside the database.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 5

I am with Paul on this one - store each piece of the name in it's own field (even though this leaves a lot of empty fields for the suffix). Even with that scheme though you still run into McAfee, McDonald, D'Angelo.... proper case is a long standing tough problem.

Edit: I should have googled before posting. Here are just a few links that turn up. As you can see, it's not easy and is a thorn in the sides of many.

http://classicasp.aspfaq.com/general/how-do-i-convert-a-name-to-proper-case.html

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527

Paul White SSC Guru Points: 150467 More actions · Answer 6

One other idea is to embed the formatting rules in a .NET function. The same function could be used in the application or the database using SQLCLR integration. Just another option.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi