March 28, 2008 at 8:47 am
Hello...
I'm completely and totally new to SQL Server 2005, so please forgive me if this is a stupid question. The Contact table in my database has FirstName, LastName, StreetAddress and City columns whose entries I'd like to have automatically convert to proper casing, i.e., each word's first letter is capitalized. I created a trigger so that the State column's entries are entirely capitalized upon insertion of each new record, but I can't seem to find anything so simple for partial capitalization. Does anyone know how to do this without miles of code? Bear in mind, again, that I'm totally new at this! 😀
Thanks!
March 28, 2008 at 10:47 am
UPPER(LEFT([State], 1)) + LOWER(SUBSTRING([State],2,LEN([State])))
Is one way of doing this, though it would be better to update your insert Stored Procs than use a trigger.
March 28, 2008 at 10:54 am
http://www.sqlservercentral.com/articles/Advanced+Querying/tamethosestringspart7/390/
FYI, be careful of using triggers for this. You might cause lots of unnecessary work and this will have to run for every insert/update. You might want to set up a job to look for problems and fix them rather than using a trigger.
March 28, 2008 at 11:17 am
Be careful about applying Northern Europe name capitialization rules to names of persons from other cultures. For example, notice the capitalization and accents for:
Gustavo Nery de Sá da Silva
José Bonifácio de Andrada e Silva
SQL = Scarcely Qualifies as a Language
March 31, 2008 at 1:16 pm
Another thing to watch out for (I burned myself this one) is Mac. For example, you want to end up with "Sean MacDonald", but not with "Joe MacY", so you can't just capitalize the first letter after "Mac". If you want namecase to be correct, though, you do need to worry about "Mc", "Mac", "D'" (as in "D'Agincourt", etc.), which can be part of a single string with no spaces in it.
Instead of a simple algorithm, I use a database of first names and last names. The first names data includes things like the equivalence of "Richard", "Rich", "Richy", "Dick", "Rick", "Ricky", etc. The last names includes things like "MacDonald" and "Macy". It also includes a gender guideline for first names. I haven't included a nationality/culture guideline for last names, but it would be a good idea. (The database is far from complete, but it's reasonably useful as-is.) I need to add an alternate spellings structure for last names, to handle things like "Gwynne", "Guinne", "Gwin", etc., for name-matching, but haven't gotten around to that yet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply