spathiphylum
asked on
Easy computed column question
i'm moving a database from Access to SQL Server and trying to create computed columns that do the work of Access Visual Basic routines. Here are the routines I'm trying to convert:
'example: "Mr.", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
'example: "Dr.", "John", "Doe" "jdoe@email.com" -> "Dr. John Doe <jdoe@email.com>"
'example: "", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
Public Function getFullEmailFor(Prefix, FirstName, LastName, Email)
getFullEmailFor = getFullNameAndTitleFor(Pre
End Function
Public Function getFullNameAndTitleFor(Pre
If (Prefix <> "Mr.") And (Prefix <> "Mrs.") And (Prefix <> "Ms.") Then
newPrefix = Prefix
Else 'Dr. Rev. Capt. etc
newPrefix = ""
End If
getFullNameAndTitleFor = newPrefix & " " & FirstName & " " & LastName
End Function
'example: "Mr.", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
'example: "Dr.", "John", "Doe" "jdoe@email.com" -> "Dr. John Doe <jdoe@email.com>"
'example: "", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
Public Function getFullEmailFor(Prefix, FirstName, LastName, Email)
getFullEmailFor = getFullNameAndTitleFor(Pre
End Function
Public Function getFullNameAndTitleFor(Pre
If (Prefix <> "Mr.") And (Prefix <> "Mrs.") And (Prefix <> "Ms.") Then
newPrefix = Prefix
Else 'Dr. Rev. Capt. etc
newPrefix = ""
End If
getFullNameAndTitleFor = newPrefix & " " & FirstName & " " & LastName
End Function
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
alter table yourtable add FullNameAndTitleFor = FirstName + ' ' + LastName + ' <' + Email + '>'
ASKER
wow. thats fast. that's a good start, but it doesn't deal with the prefix: "Dr.", "Mr.", etc. see the examples in the question
sorry, I didn't see you had 2 functions ..
alter table yourtable
add FullNameAndTitleFor = coalesce( Prefix + ' ' , '' ) + FirstName + ' ' + LastName
alter table yourtable
add FullEmailFor = FirstName + ' ' + LastName + ' <' + Email + '>'
alter table yourtable
add FullNameAndTitleFor = coalesce( Prefix + ' ' , '' ) + FirstName + ' ' + LastName
alter table yourtable
add FullEmailFor = FirstName + ' ' + LastName + ' <' + Email + '>'
ASKER
here are the two examples:
'example: "Mr.", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
'example: "Dr.", "John", "Doe" "jdoe@email.com" -> "Dr. John Doe <jdoe@email.com>"
note that if the second one has the title, but not the first. i don't want to show the title if it is "Mr." "Mrs." "Ms."
'example: "Mr.", "John", "Doe" "jdoe@email.com" -> "John Doe <jdoe@email.com>"
'example: "Dr.", "John", "Doe" "jdoe@email.com" -> "Dr. John Doe <jdoe@email.com>"
note that if the second one has the title, but not the first. i don't want to show the title if it is "Mr." "Mrs." "Ms."
sorry... it's time for me to go to bed :-)
alter table yourtable
add FullEmailFor = coalesce( Prefix + ' ' , '' ) +FirstName + ' ' + LastName + ' <' + Email + '>'
alter table yourtable
add FullEmailFor = coalesce( Prefix + ' ' , '' ) +FirstName + ' ' + LastName + ' <' + Email + '>'
ASKER
that's not going to work. if the title is "Mr." then it will print "Mr. John Doe" instead of "John Doe" (see the examples above)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.