Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ZoneFX
Starting Member
17 Posts |
 Posted - 2008-03-29 : 05:43:50
|
| I've used this udf for a while with great success, but only on fields with more than one word....http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspxI'd like to know how I can adapt this function so it will convert a scottish/irish surname (McDonald or O'Shea) when there is only the surname in the columnThis is what I'd been using for multiple words (Ronald McDonald). But it won't work on just Mcdonald. I'm sure it's just a simple tweak, but it all looks Punjabi to me?Thanks in advance!!CREATE FUNCTION [dbo].[f_ProperCase](@Text as varchar(512)) RETURNS varchar(512) asBEGINDECLARE @Reset bitDECLARE @Ret varchar(512)DECLARE @i intDECLARE @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 CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1 ELSE 0 END = 1 THEN 1 ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 ELSE 1 END END,@i = @i +1RETURN @Ret -- Test: SELECT dbo.f_ProperCase('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )END |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-03-30 : 10:58:06
|
You need to make a list of exceptions and put them in a table. When the normal proper casing is done, check the exception table against the final poroper casing and make the appropriate replacements.--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] |
 |
|
|
ZoneFX
Starting Member
17 Posts |
Posted - 2008-03-31 : 14:42:10
|
| The function as above will do everything I want. I just wondered if their was a tweak to this function to make it work for a single word. I don't understand any of what is written. And as useful as your reply may be, I have no idea how to "make a list of exceptions and put them in a table. When the normal proper casing is done, check the exception table against the final poroper casing and make the appropriate replacements."That sounds just as foreign as the function code? A reaql life piece of code is what works best for me......Sorry, I'm a Neophite and sound a bit thick!! But, you do need to start somewhere ... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-31 : 15:37:45
|
quote:
Originally posted by ZoneFX..I just wondered if their was a tweak to this function to make it work for a single word...
If it works OK for multiple words, try adding a dummy word in front.CODO ERGO SUM |
|
|
|
ZoneFX
Starting Member
17 Posts |
Posted - 2008-03-31 : 17:17:51
|
| Brilliant!! Great how a few minds can work!! Again, I'm very new at this. I did the following altough I'm concerned it's a bit "ugly", but it worked!!! And will save me many hours going through 5,000 plus records and updating those pesky celts manually...UPDATE namesSET surname =('dave' + ' ' + surname);UPDATE namesSET surname = dbo.f_ProperCase(surname);UPDATE namesSET surname = replace(surname,'Dave ','');If my sql is as ugly as I'm assuming, Anyone advise a better way of adding a forname and space preforming the function and then removing the added and now correctly cased forename and space...Learning curve!! |
|
|
|
|
|
|
|
|
Proper case conversion for celtic names