## Proper Case Problem

 Author Message Kicking_Fish0713 SSC-Addicted Group: General Forum Members Points: 428 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 Bhuvnesh SSC-Dedicated Group: General Forum Members Points: 32340 Visits: 4079 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;-) Rob Schripsema SSCarpal Tunnel Group: General Forum Members Points: 4937 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 SchripsemaPropack, Inc. Bhuvnesh SSC-Dedicated Group: General Forum Members Points: 32340 Visits: 4079 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 Group: General Forum Members Points: 79706 Visits: 11400 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 WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi Uripedes Pants SSCrazy Group: General Forum Members Points: 2151 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.htmlhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527 Paul White SSC Guru Group: General Forum Members Points: 79706 Visits: 11400 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 WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi