## Proper Case Problem

 Author Message Kicking_Fish0713 SSC Rookie 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 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 SSCrazy 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'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 SSCrazy 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 SchripsemaPropack, Inc. Bhuvnesh SSCrazy 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 SSChampion 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 WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi Uripedes Pants Say Hey Kid 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.htmlhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527 Paul White SSChampion 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 WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi