SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Proper Case Problem


Proper Case Problem

Author
Message
Kicking_Fish0713
Kicking_Fish0713
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12504 Visits: 4077
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3125 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12504 Visits: 4077
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
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33598 Visits: 11359
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 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
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33598 Visits: 11359
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