Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Proper Case Problem Expand / Collapse
Author
Message
Posted Monday, March 29, 2010 11:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 21, 2012 1:19 AM
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

Post #892466
Posted Tuesday, March 30, 2010 4:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #892598
Posted Tuesday, March 30, 2010 9:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:50 AM
Points: 1,994, Visits: 10,965
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
Accelitec, Inc
Post #892978
Posted Tuesday, March 30, 2010 11:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #893489
Posted Thursday, April 1, 2010 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 11,194, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #894803
Posted Thursday, April 1, 2010 9:01 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 7, 2014 11:41 AM
Points: 703, Visits: 4,519
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
Post #894889
Posted Thursday, April 1, 2010 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 11,194, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #894897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse