Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have three database fields, fname, mname, and lname which represent first name, middle name, and last name in my contacts table. If the contact is a company, nothing is input into first name or middle name, just last name. See example below

    fname, mname, lname
    Jim, Edward, Jones
    Home Depot
    Sally, Jones

    As you can tell, sometimes I don't have a first name or a middle name. How could I go about concatenating these fields via SQL. I wrote the following, but it appears I can't get rid of the extra space if no middle name exists:

    PrimaryName: Trim(IIF([fname]="","",[fname]) & " " & IIF([mname]="","",[mname]) & " " & [lname])

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by wino View Post
    I have three database fields, fname, mname, and lname which represent first name, middle name, and last name in my contacts table. If the contact is a company, nothing is input into first name or middle name, just last name. See example below

    fname, mname, lname
    Jim, Edward, Jones
    Home Depot
    Sally, Jones

    As you can tell, sometimes I don't have a first name or a middle name. How could I go about concatenating these fields via SQL. I wrote the following, but it appears I can't get rid of the extra space if no middle name exists:

    PrimaryName: Trim(IIF([fname]="","",[fname]) & " " & IIF([mname]="","",[mname]) & " " & [lname])
    I'd do it this way:

    PrimaryName: fname & " " + mname & " " + lname
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Just to add an explanation of Mark's reply.

    The + operator acts like the & operator, but it only operates if both parts are not Null.

    So " " + [mname] does not return a space if there is no mname.

    Secondly the + operator has higher priority than & so the expression is evaluated as if it had brackets in it, like this

    PrimaryName: fname & (" " + mname) & (" " + lname)
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The standard way to do this in Sql, without relying on any of the assumptions described in John Hutchinson's response (which I've never heard of), is:
    ISNULL(Fname + ' ', '') + ISNULL(MName + ' ', '') + LName AS PrimaryName
    Note: That's a space between the first of each pair of single quotes, and nothing between the second of each pair.

    In normal Sql (and I don't know what flavour you're using, nor what flavour John Hutchison is describing), concatenating anything at all to a Null value returns a Null value.
    So we concatenate a space to FName. If that results in a Null, we return a zero-length string instead. Similarly with MName.
    If your flavour of Sql is Microsoft Access, then use this syntax instead:
    PrimaryName: NZ(Fname & " ", "") & NZ(MName & " ", "") & LName

    If, when you say you sometimes don't have a first or a middle name, you mean that you do have such values, but that they equal a zero-length string (which is completely different to Null) then I'd use a Case statement, as in:
    CASE WHEN FName = '' THEN '' ELSE FName + ' ' END
    + CASE WHEN MName = '' THEN '' ELSE MName + ' ' END
    + LName AS PrimaryName

    Again, for Access, the syntax is:
    PrimaryName: IIF(Fname = "", "", Fname & " ")
    & IIF(MName = "", "", MName & " ")
    & LName

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    My response was referring to Access SQL where the solution provided by Mark is a very common solution. I use it regularly.

    In Access SQL [firstname] & " " returns " " if [firstname] is Null. [firstname] + " " returns Null if [firstname] is Null.

    I attach a little demo.
    [attachment=88270:concatenate.zip]
    Attached Files Attached Files
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    My response was referring to Access SQL where the solution provided by Mark is a very common solution. I use it regularly.

    In Access SQL [firstname] & " " returns " " if [firstname] is Null. [firstname] + " " returns Null if [firstname] is Null.
    Well, I've always found the Access flavour of Sql pretty horrible, but I didn't realize just how bad it is (knowing SQL well, I never even considered trying the syntax Mark supplied). This is not only non-standard SQL, it's actually wrong.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If by wrong you mean it doesn't conform to ANSI SQL, you are correct - Access SQL (Jet) has never claimed to be compliant with ANSI unless you choose that option, and most Access developers don't. This forum content is primarily Access, so the old-timers tend to assume that is what the poster meant unless they specify what product they are using.
    Wendell

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    If by wrong you mean it doesn't conform to ANSI SQL, you are correct - Access SQL (Jet) has never claimed to be compliant with ANSI unless you choose that option, and most Access developers don't. This forum content is primarily Access, so the old-timers tend to assume that is what the poster meant unless they specify what product they are using.
    I think there's a huge difference between "it doesn't conform" and "it's wrong"!

    For example, Access SQL insists on the use of brackets for JOIN clauses where none is needed in "real" SQL. That's non-conforming.
    But when Access SQL returns a value (other than NULL) for a NULL value concatenated to some string, then that's simply wrong. It's like saying "1 + 1 = 2" except in Access SQL, where it equals 3!

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Be that as it may, it is a common Access "trick" that is widely documented - see Concatenation of Microsoft Access Data for example. The ampersand (&) is the Microsoft preferred operator (non-standard of course) which grew out of Access Basic and does behave in the expected fashion. Because I work across both Access and SQL Server I try to avoid using tricks that won't work in both.
    Wendell

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Paul Thornett View Post
    The standard way to do this in SQL, without relying on any of the assumptions described in John Hutchinson's response (which I've never heard of),...
    These aren't assumptions; this is a known, proven, and documented method of concatenation that Access allows. If you are an Access developer, you learn it and use it (and you use it alot). If you aren't using Access, then why do you care what we do?

    Many of us Access "old-timers" have been hearing for years that Access SQL is not "real" SQL (and Access is not a "real" database, etc.), and quite frankly we don't care anymore. Way, way back in the days of the old Access forum on Compuserve (like 10-15 years ago), Joe Celko was a frequent visitor, and he used to periodically bring-up the same arguments. Quite frankly, I think he did it to provoke a reaction; once people stopped rising to the bait he seemed less interested in pursuing the issue.

    Our purpose for visiting the Lounge is not to defend Access, rather it is to help those who develop in Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Our purpose for visiting the Lounge is not to defend Access, rather it is to help those who develop in Access.
    Surely one of the goals of any software is scalability. God knows it's hard enough to do a decent job of converting an Access database to SQL Server (surely one of the more common requirements) without having to remove all the specific-to-Access SQL foibles that Access is prone to. Where there is a legitimate way of doing something that is common to many flavours of SQL (e.g. the ISNULL / NZ syntax I used above), then shouldn't we be adopting that approach rather than taking what may be a blinkered view and simply recommending "what works", even though it may cause grief later on?

    It's quite certain that neither you nor I have any idea whether the application in question may, in the future, become a candidate for "upsizing", but if we can recommend an approach that minimizes the amount of work that needs to be done in that event, then shouldn't we be recommending it?

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Paul Thornett View Post
    Surely one of the goals of any software is scalability. God knows it's hard enough to do a decent job of converting an Access database to SQL Server (surely one of the more common requirements) without having to remove all the specific-to-Access SQL foibles that Access is prone to. Where there is a legitimate way of doing something that is common to many flavours of SQL (e.g. the ISNULL / NZ syntax I used above), then shouldn't we be adopting that approach rather than taking what may be a blinkered view and simply recommending "what works", even though it may cause grief later on?

    It's quite certain that neither you nor I have any idea whether the application in question may, in the future, become a candidate for "upsizing", but if we can recommend an approach that minimizes the amount of work that needs to be done in that event, then shouldn't we be recommending it?
    I can't speak for anyone but myself, but in my experience, converting an Access database to SQL Server is definitely NOT a "common requirement".

    Do you actually think you are the first person ever to raise these arguments about Access SQL? Since apparently you just discovered "The Lounge", did you think you could just drop in and convert everyone? There are several of us here who have had this discussion many times before over the years. As for me, I don't feel the need to rehash it anymore; life is too short. I'm done.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •