Anyone know how to contruct a Proper case in SQL? 1

[Clipper2000]

Lower case = LCase
Upper case = UCase
Proper case = ??

Thanks.

 

[buddafish]

Like capital first letter in first and last names ?

 

[NorthNone]

Author of this module: David McAfee at dmcafee@pacbell.net
-----------------------------------------
Option Compare Database
Option Explicit
Function fProperCase(Optional strText As String, Optional blPrompt As Boolean) As String
'Call this function in this manner:
' ProperCase(yourTexthere)
' If you would like to be prompter on 2 or 3 character words, then call in this manner:
' ProperCase(yourTexthere,1)

'If any improvements/clean up/errors found in this code, please
'email David McAfee at dmcafee@pacbell.net

If Nz(strText, "") <> "" Then 'If value is not blank, then continue below
Dim intCounter As Integer
Dim OneChar As String
Dim StartingNumber As Integer
StartingNumber = 1
'*********************** 3 Letter Check
If Right(Left(strText, 4), 1) = " " Then 'Check for 3 character words at the start of string
'The line below checks for all 3 characters to be equal, and capitalize if so "AAA" or "EEE Movers"
'The next line, prompts the user if they would like to capaitalize the 3 letter word, this can be replaced
'with something like: If Left(strText,3) IN ("ADP", "TMT") or like: If Left(strText,3) NOT IN ("Joe", "Bob")
'If you'd rather not prompt the user
If (Left(strText, 1) = Mid$(strText, 2, 1) And Left(strText, 1) = Mid$(strText, 3, 1)) Or _
(blPrompt = True And AskToConvert(strText, Left(strText, 3), 3, blPrompt) = 6) Then
'Capitalize the 3 char's
strText = UCase(Left$(strText, 3)) & LCase$(Mid$(strText, 4, 255))
StartingNumber = 4
Else
'Only capitalize the first of the 3 char's
strText = UCase$(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If
'******************** End 3 letter check
'******************** Start 2 Letter word check
ElseIf Right(Left(strText, 3), 1) = " " Then 'Check for 2 character words as the start of the string
If Left(strText, 1) = Mid$(strText, 2, 1) Or _
(blPrompt = True And AskToConvert(strText, Left(strText, 2), 2, blPrompt) = 6) Then
'Capitalize the 2 char's
strText = UCase(Left$(strText, 2)) & LCase$(Mid$(strText, 3, 255))
StartingNumber = 3
Else
'Only capitalize the first of the 2 char's
strText = UCase(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If
'***************** End 2 Letter Check
Else
'Convert first character to capital then the rest to lowercase.
strText = UCase$(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If
'Look at each character, starting at the second character.
For intCounter = StartingNumber To Len(strText)
OneChar = Mid$(strText, intCounter, 1)
Select Case OneChar
'...convert the character after dash/hyphen/slash/period/ampersand to uppercase.
' Such as "A.B.C. Industries", B&B Mfg
Case "-", "/", ".", "&"
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
Case "'" 'Check the character two places after the apostrophe.
If Mid$(strText, intCounter + 2, 1) <> " " Then 'If it is not a space, then capatilize (O'Conner, O'Niel)
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
Else
'Do nothing as in "Don't" , "Tom's Diner", "haven't", "I'm"
End If
Case "c" ' Take care of the McAfee's, McDonalds & McLaughlins and such
If (Mid$(strText, intCounter - 1, 1) = "M") Then 'Check if Prev Char is an M
If ((intCounter - 2) < 1) Then 'Check to see if the M was the first character
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
ElseIf (Mid$(strText, intCounter - 2, 1) = " ") Then
'If M wasn't first character, then check to see if a space was before the M, so we don't capitalize Tomchek or Adamczyk
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End If
End If
Case " "
Select Case Mid$(strText, intCounter + 1, 3)
Case "de " 'Add any other exceptions here Example: Oscar de La Hoya"
strText = Left$(strText, intCounter) & LCase$(Mid$(strText, intCounter + 1, 3)) & Mid$(strText, intCounter + 4, 255)
intCounter = intCounter + 3
Case Else ' Example: A B C Manufacturing
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End Select
If Mid$(strText, intCounter + 1, 9) = "diMartini" Then 'Add any other odd balls in this fashion
strText = Left$(strText, intCounter) & "diMartini" & Mid$(strText, intCounter + 10, 255)
End If
'*********************** Check for 3 character word *******************
If Mid$(strText, intCounter + 4, 1) = " " Then 'Check for 3 letter words
If (Mid$(strText, intCounter + 1, 1) = Mid$(strText, intCounter + 2, 1) And _
Mid$(strText, intCounter + 1, 1) = Mid$(strText, intCounter + 3, 1)) Or _
(blPrompt = True And AskToConvert(strText, Mid$(strText, intCounter + 1, 3), 3, blPrompt) = 6) Then
'Capitalize the 3 char's
strText = Left$(strText, intCounter) & UCase(Mid$(strText, intCounter + 1, 3)) & Mid$(strText, intCounter + 4, 255)
intCounter = intCounter + 3
Else
'Only capitalize the first of the 3 char's
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End If
'********************** check for 2 char words *******************
ElseIf Mid(strText, intCounter + 3, 1) = " " Then 'Check for 2 letter words
If (Mid(strText, intCounter + 1, 1) = Mid(strText, intCounter + 2, 1)) Or _
(blPrompt = True And AskToConvert(strText, Mid(strText, intCounter + 1, 2), 2, blPrompt) = 6) Then
'Capitalize the 2 char's
strText = Left$(strText, intCounter) & UCase(Mid(strText, intCounter + 1, 2)) & LCase$(Mid$(strText, intCounter + 3, 255))
intCounter = intCounter + 2
Else
'Only capitalize the first of the 2 char's
strText = Left$(strText, intCounter) & UCase(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
intCounter = intCounter + 1
End If
'******************** END 2 LETTER CHECK
End If
Case Else
End Select
Next
Else
strText = ""
End If
'All done, return current contents of strText variable.
fProperCase = strText

End Function
Private Function AskToConvert(strWholeText As String, strSelection As String, NumOfChars As Integer, blPrompt As Boolean) As Integer
Dim txtNumChars As String
Dim Msg As String
Dim MsgBoxType As Integer
If blPrompt = True Then
MsgBoxType = 4
Select Case NumOfChars
Case 2
Msg = "In the example '" & strWholeText & "', shall I capitalize the two letter word '" & strSelection & "' to '" & UCase(strSelection) & "' ?"
Case 3
Msg = "In the example '" & strWholeText & "', shall I capitalize the three letter word '" & strSelection & "' to '" & UCase(strSelection) & "' ?"
Case Else
Msg = "Developer called Function AskToConvert from Function ProperCase but did not specify NumOfChars"
MsgBoxType = 0
End Select
AskToConvert = MsgBox(Msg, MsgBoxType, "Proper Case Function")
Else
AskToConvert = 0
End If
End Function


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[Clipper2000]

I found the solution over the internet !

Here is the code:

Function PCase(strInput)
    Dim iPosition  ' Our current position in the string (First character = 1)
    Dim iSpace     ' The position of the next space after our iPosition
    Dim strOutput  ' Our temporary string used to build the function's output

    ' Set our position variable to the start of the string.
    iPosition = 1
    
    ' We loop through the string checking for spaces.
    ' If there are unhandled spaces left, we handle them...
    Do While InStr(iPosition, strInput, " ", 1) <> 0
        ' To begin with, we find the position of the offending space.
        iSpace = InStr(iPosition, strInput, " ", 1)
        
        ' We uppercase (and append to our output) the first character after
        ' the space which was handled by the previous run through the loop.
        strOutput = strOutput & UCase(Mid(strInput, iPosition, 1))
        
        ' We lowercase (and append to our output) the rest of the string
        ' up to and including the current space.
        strOutput = strOutput & LCase(Mid(strInput, iPosition + 1, iSpace - iPosition))

        ' Note:
        ' The above line is something you may wish to change to not convert
        ' everything to lowercase.  Currently things like "McCarthy" end up
        ' as "Mccarthy", but if you do change it, it won't fix things like
        ' ALL CAPS.  I don't see an easy compromise so I simply did it the
        ' way I'd expect it to work and the way the VB command
        ' StrConv(string, vbProperCase) works.  Any other functionality is
        ' left "as an exercise for the reader!"
        
        ' Set our location to start looking for spaces to the
        ' position immediately after the last space.
        iPosition = iSpace + 1
    Loop

    ' Because we loop until there are no more spaces, it leaves us
    ' with the last word uncapitalized so we handle that here.
    ' This also takes care of capitalizing single word strings.
    ' It's the same as the two lines inside the loop except the
    ' second line LCase's to the end and not to the next space.
    strOutput = strOutput & UCase(Mid(strInput, iPosition, 1))
    strOutput = strOutput & LCase(Mid(strInput, iPosition + 1))

    ' That's it - Set our return value and exit
    PCase = strOutput
End Function

 

[lespaul]

we had to build a function for it. I'll see if I can find it.

Leslie

 

[Golom]

Or just

StrConv ("convert this to proper case", 3)

Yields

"Convert This To Proper Case

 

[buddafish]

Golom,

Nice One!

scottie