Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Proper Case Problem


Proper Case Problem

Author
Message
Kicking_Fish0713
Kicking_Fish0713
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 124
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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
Rob Schripsema
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 11042
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Uripedes Pants
Uripedes Pants
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 4519
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search