Proper / title case in t-sql

问题:

I'm using SQL 2012 RC0, and looking for a function that can convert a column that holds first and last names into proper/title case. I've had a look around SO but can't seem to find anything, if there is another post please let me know and I will close this one.

The table column I want to convert is filled from a user entered field from a web front-end, so at times is not entered in a format that can be used for the purpose of populating name fields on reports for customers (appreciate validation at the front-end can be made stricter, however this doesn't solve the problem of the customers already in the database).

Does anyone know how to do this in t-sql? Does SQL 2012 have any string functions available to perform this? Or should I look for a way to do this via CLR (C#) stored proc?

回答1:

Proper case is something that is so hard to get right. Think names like Van der Wilden and VanWyck. I wrote a T-SQL function years ago but other than going with CLR there really isn't anything new in SQL Server 2012 that will help with this:

http://web.archive.org/web/20120215192418/http://classicasp.aspfaq.com/general/how-do-i-convert-a-name-to-proper-case.html

P.S. why are you still using RC0? RTM (11.0.2100) was released today...

回答2:

If you have MDS installed (SQL 2005 and up) you could handle case properly through regular expressions.

You would need to add a bit more to it to handle the mentioned "Van der Wilden" but it could be extended to even handle this as well.

CASE 
WHEN mds.mdq.RegexIsMatch([First Name],'[A-Z]{1,}(\s|\-)[A-Z]{2,}',7)=1 THEN

UPPER(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)(\s|\-)','First',7),1,1))+lower(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)(\s|\-)','First',7),2,LEN(mds.mdq.RegexExtract([First Name],'(?<First>.*)(\s|\-)','First',7))-1))
+ mds.mdq.RegexExtract([First name],'(?<Sep>(\s|\-))','Sep',7) + SUBSTRING(mds.mdq.RegexExtract([First Name],'(\s|\-)(?<MI>.*)','MI',7),1,1)+ LOWER(SUBSTRING(mds.mdq.RegexExtract([First Name],'(\s|\-)(?<MI>.*)','MI',7),2,LEN(mds.mdq.RegexExtract([First Name],'(\s|\-)(?<MI>.*)','MI',7))-1))

WHEN mds.mdq.RegexIsMatch([First Name],'[A-Z]{1,}\s[A-Z]{1}',7)=1 THEN

UPPER(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)\s','First',7),1,1))+
lower(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)\s','First',7),2,LEN(mds.mdq.RegexExtract([First Name],'(?<First>.*)\s','First',7))-1))
+ ' ' + mds.mdq.RegexExtract([First Name],'\s(?<MI>.*)','MI',7)+'.'
ELSE
UPPER(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)','First',7),1,1))+
LOWER(SUBSTRING(mds.mdq.RegexExtract([First Name],'(?<First>.*)','First',7),2,LEN(mds.mdq.RegexExtract([First Name],'(?<First>.*)','First',7))-1))
END 
,
[Last Name]=CASE
WHEN mds.mdq.RegexIsMatch([Last Name],'(\s|\-)',7)=1
THEN
SUBSTRING(mds.mdq.RegexExtract([Last Name],'(?<Maiden>.*)(\s|\-)','Maiden',7),1,1)+LOWER(SUBSTRING(mds.mdq.RegexExtract([Last Name],'(?<Maiden>.*)(\s|\-)','Maiden',7),2,LEN(mds.mdq.RegexExtract([Last Name],'(?<Maiden>.*)(\s|\-)','Maiden',7))-1))+
mds.mdq.RegexExtract([last name],'(?<Sep>(\s|\-))','Sep',7)+
SUBSTRING(mds.mdq.RegexExtract([Last Name],'(\s|\-)(?<Maiden>.*)','Maiden',7),1,1)+LOWER(SUBSTRING(mds.mdq.RegexExtract([Last Name],'(\s|\-)(?<Maiden>.*)','Maiden',7),2,LEN(mds.mdq.RegexExtract([Last Name],'(\s|\-)(?<Maiden>.*)','Maiden',7))-1))
Else
SUBSTRING([Last Name],1,1) +LOWER(SUBSTRING([last name],2,LEN([Last Name])-1))
END