Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Proper Case Problem Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 29, 2010 11:26 PM
 SSC 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 BBTo this format:Aa BbMy problem is,how can I set/change the values ending with roman numeral? Like,FROM this format:AA BB II or AA BB IIITO this format:Aa Bb II or Aa Bb IIIFunction script:create function ProperCase(@Text as varchar(8000))returns varchar(8000)asbegin 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 @Retend
Post #892466
 Posted Tuesday, March 30, 2010 4:59 AM
 SSCrazy Group: General Forum Members Last Login: Friday, November 29, 2013 3:57 AM Points: 2,816, Visits: 3,895
 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'unionSELECT 'DEXY V'UNIONSELECT '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 Group: General Forum Members Last Login: 2 days ago @ 11:20 AM Points: 1,799, Visits: 10,920
 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 SchripsemaAccelitec, Inc
Post #892978
 Posted Tuesday, March 30, 2010 11:45 PM
 SSCrazy Group: General Forum Members Last Login: Friday, November 29, 2013 3:57 AM Points: 2,816, Visits: 3,895
 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 01, 2010 8:10 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 9:18 PM Points: 11,051, Visits: 10,808
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #894803
 Posted Thursday, April 01, 2010 9:01 AM
 Say Hey Kid Group: General Forum Members Last Login: Yesterday @ 2:22 PM Points: 664, Visits: 4,299
 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.htmlhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527
Post #894889
 Posted Thursday, April 01, 2010 9:06 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 9:18 PM Points: 11,051, Visits: 10,808
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #894897

 Permissions