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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Really nasty!! Where do I start???

Author  Topic 

Tim_Field
Yak Posting Veteran

87 Posts
Posted - 2004-06-11 : 10:19:03
Hi all,

I'm stuck on this! and don't know where to start, perhaps you could point me in the right direction.

I've got hold of a piece of code to sort out some names in a sql server database. It does the following (as per Sql server)......

• First character in each name is capitalised
• Hyphenated names will be additionally capitalised after each hyphen
• First letter after a space should be capitalised
• Gaelic type names must be capable of correctly placing capitalised letters, e.g. McGowan, Macdonald, O’Malley
• Numbers following a name must also be displayed, e.g. Lee Peters IV
• If Junior or Senior follows the surname, these must also be included e.g. Jnr, Snr.
Refer to Section 8 Issues for further information/

---------------------------------------------------

Here is the best perl algorithm I could find for "Name Casing". Translating to SQL Server is a little beyond me. I can explain the weird RegExen if it isn't obvious, and it probably isn't.

1. Lowercase everything

2. Uppercase the first letter of every word
s/\b(\w)/\u$1/g;

3. Lowercase 's
s/(\'s)\b/\L$1/

4. Deal with Mcs and Macs, excluding names with 1-2 letters after prefix like
Mack, Macky, Mace, and excluding names ending int a,c,i,o, or j, which are
typically Polish or Italian

if ( /\bMac[A-Za-z]{2,}[^aciozj]\b/o or /\bMc/o ) {
s/\b(Ma?c)([A-Za-z]+)/$1\u$2/g ;

# Now correct for "Mac" exceptions
s/\bMacEvicius/Macevicius/g ; # Lithuanian
s/\bMacHado/Machado/g ; # Portuguese
s/\bMacHar/Machar/g ;
s/\bMacHin/Machin/g ;
s/\bMacHlin/Machlin/g ;
s/\bMacIas/Macias/g ;
s/\bMacIulis/Maciulis/g ;
s/\bMacKie/Mackie/g ;
s/\bMacKle/Mackle/g ;
s/\bMacKlin/Macklin/g ;
s/\bMacQuarie/Macquarie/g ;

}

5. Known exception
s/Macmurdo/MacMurdo/g;

6. Fixes for "son (daughter) of" etc. in various languages.
s{ \b Al(?=\s+\w) }{al}gx ; # al Arabic or forename Al.
s{ \b Ap \b }{ap}gx ; # ap Welsh.
s{ \b Ben(?=\s+\w) }{ben}gx ; # ben Hebrew or forename Ben.
s{ \b Dell([ae])\b }{dell$1}gx ; # della and delle Italian.
s{ \b D([aeiu]) \b }{d$1}gx ; # da, de, di Italian; du French.
s{ \b De([lr]) \b }{de$1}gx ; # del Italian; der Dutch/Flemish.
s{ \b El \b }{el}gx unless $SPANISH ; # el Greek or El Spanish.
s{ \b La \b }{la}gx unless $SPANISH ; # la French or La Spanish.
s{ \b L([eo]) \b }{l$1}gx ; # lo Italian; le French.
s{ \b Van(?=\s+\w) }{van}gx ; # van German or forename Van.
s{ \b Von \b }{von}gx ; # von Dutch/Flemish

7. Fixes for roman numeral names, e.g. Henry VIII, up to 89, LXXXIX
s{ \b ( (?: [Xx]{1,3} | [Xx][Ll] | [Ll][Xx]{0,3} )?
(?: [Ii]{1,3} | [Ii][VvXx] | [Vv][Ii]{0,3} )? ) \b }{\U$1}gx ;

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-06-11 : 12:57:26
To lowercase everything, use LOWER(ColumnName). To uppercase the first letter of every word, you would use substring with patindex to find the spaces and upper.

If you provided examples of each of them, we would better be able to help you.

Tara
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-06-11 : 13:37:15
Fortunately or unfortunately (depending on your point of view) there's no Regular Expression parser in SQL 2000, although there is PATINDEX (see BOL) which has some similarity.

I've seen some threads in the past on name capitalization, but I don't have the URL handy for any of those threads.
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-06-11 : 22:27:01
Hmmmmm...

http://sqlteam.com/item.asp?ItemID=13947
Go to Top of Page

Tim_Field
Yak Posting Veteran

87 Posts
Posted - 2004-06-15 : 09:48:50
Hi Tara,

"If you provided examples of each of them, we would better be able to help you."

Tim J Field stays the same
tim j field should be Tim J Field
tim j mc'havish should be Tim J Mc'Havish
tim j samwell-jones should be Tim J Samwell-Jones
tim j field jnr should be Tim J Field Jnr
tim j field IV should stay be Tim J Field IV (NOT AS IMPORTANT!!!!)

Hope this is clear....

Thanks, Tim



===========================================================
• First character in each name is capitalised
• Hyphenated names will be additionally capitalised after each hyphen
• First letter after a space should be capitalised
• Gaelic type names must be capable of correctly placing capitalised letters, e.g. McGowan, Macdonald, O’Malley
• Numbers following a name must also be displayed, e.g. Lee Peters IV
• If Junior or Senior follows the surname, these must also be included e.g. Jnr, Snr.
Refer to Section 8 Issues for further information/
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-06-15 : 10:11:52
This is relatively specific... but it might help:

Declare @myTable table (FullName nvarchar(100))

Insert Into @myTable Select FullName = 'tim j field'
Insert Into @myTable Select FullName = 'tim j mc''havish'
Insert Into @myTable Select FullName = 'tim j samwell-jones'
Insert Into @myTable Select FullName = 'tim j field jnr'
Insert Into @myTable Select FullName = 'tim j field iv'


Select * from @myTable

Declare @counter int
Set @counter = 0
While @counter < 26
Begin
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter))))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter))))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter))))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter))))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter))))) From @myTable

Set @counter = @counter + 1
End

Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Ii ', ' II '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Iii ', ' III '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Iv ', ' IV '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Vi ', ' VI '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Vii ', ' VII '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Viii ', ' VIII '))) From @myTable
Update @myTable Set fullName = rtrim(ltrim(Replace(' ' + fullName + ' ', ' Ix ', ' IX '))) From @myTable


Select * From @myTable
--Select * From @letters

Corey
Go to Top of Page

JasonGoff
Posting Yak Master

158 Posts
Posted - 2004-06-15 : 10:20:25
Ack, 2 threads for the same issue - nightmare !! :-)

See post on other thread here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36304
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site