Caps to mixed case

[jmj]

I am doing a dump from a DBIV to a SQL database. In the DBIV they hold everything in capital letters. For formatting purposes I need to get it so Firstname/Lastname show up as mixed case. I can do that easily enough- uppercase the first letter-all others lower case.
This is ok but I'm having a harder time with the "exceptions"
1. Last name with a space ie "Van Snooze"
2. Parenthesis used to show formal vs. common name ie: Joseph (JJ)
3. - used in the Last name ie: Smith-Jacobs
Can anyone help me at least get started in doing this? I have done it successfully in a regular programming language but not sure how to move that to SQL and for effeciency sake I want to make this a stored procedure.
Thanks!

 

[jhall156]

This kind of data scrubbing sucks! If you want to show us what you have in other languages I feel sure we can help you port it to T-SQL. JHall

 

[RickCole]

Have a look at the following link:

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14117

This might be just what you need.

Rick.

 

[jmj]

Ok that article makes me wish I knew C. But since I don't I guess what I should try is several simple sql statements and run them?
I've done mine in ColdFusion and so it's basically a bunch of if/then run update.
Here's how I did it in coldfusion

Query to pull LastNames called GetLastNames

<CFLOOP QUERY=&quot;GetLastNames&quot;>
<!---First set 1st letter to uppercase and the rest to lower case--->

<CFSET NAME = &quot;#UCase(Left(GetLastNames.LASTNAME1, 1))##LCase(Right(GetLastNames.LASTNAME1, Len(GetLastNames.LASTNAME1) - 1))#&quot;>

<!---Look for Names that have - in their name--->
<CFSET hyphen_test=findnocase(&quot;-&quot;,LASTNAME1,1)>
<CFSET STUDENTID=GetLastNames.STUDENTID>
<CFIF hyphen_test NEQ 0>
<!--- the next code does all of the work and puts it into the variable
newname--->
<CFSET NAME=ucase(left(ListGetAt(LASTNAME1, 1,
&quot;-&quot,1))&right(lcase(ListGetAt(LASTNAME1, 1,
&quot;-&quot),len(lcase(ListGetAt(LASTNAME1, 1,
&quot;-&quot))-1)&&quot;-&quot;&ucase(left(ListGetAt(LASTNAME1, 2,
&quot;-&quot,1))&right(lcase(ListGetAt(LASTNAME1, 2,
&quot;-&quot),len(lcase(ListGetAt(LASTNAME1, 2, &quot;-&quot))-1)>
<CFELSE>
</CFIF>

<!---Look for names that are 2 words---->
<CFSET SpaceBreak_test=findnocase(&quot; &quot;,LASTNAME1,1)>
<CFSET STUDENTID=GetLastNames.STUDENTID>

<CFIF SpaceBreak_test NEQ 0>

<CFIF Len(ListGetAt(LASTNAME1, 2,
&quot; &quot) GTE 2>
<CFSET NAME=ucase(left(ListGetAt(LASTNAME1, 1,
&quot; &quot,1))&right(lcase(ListGetAt(LASTNAME1, 1,
&quot; &quot),len(lcase(ListGetAt(LASTNAME1, 1,
&quot; &quot))-1)&&quot; &quot;&ucase(left(ListGetAt(LASTNAME1, 2,
&quot; &quot,1))&right(lcase(ListGetAt(LASTNAME1, 2,
&quot; &quot),len(lcase(ListGetAt(LASTNAME1, 2, &quot; &quot))-1)>
</CFIF>
</CFIF>


<CFQUERY NAME=&quot;UpdateName&quot; DATASOURCE=&quot;#Request.dsn#&quot;>
UPDATE StudentFile
SET LASTNAME1='#NAME#'
WHERE STUDENTID='#STUDENTID#'
</CFQUERY>

</CFLOOP>
So I run through these for all the scenerios we can think of -- Jr. Sr./Smith-Jacob/ Van Snnoze/Tom (TT)/D'erth/etc....

 

[jhall156]

There's no C in there, all T-SQL unless we're looking at different sites... JHall