SQL isnull issue

Posted on 2011-10-14
Last Modified: 2012-05-12
Hello experts -

I know the answer to this one but I just cannot seem to remember it.

I have created a forumla to concatenate name fields but am having a problem if there is a middle name.  I have placed the isnull command in the appropriate place so if the constituent has no middle name it will be replaced with '', but I cannot seem to figure out how to put the a space between the middle name and the last name if the constituent has a middle name.  For example,
John Doe shows as John Doe but John H. Doe shows as John H.Doe.  I have attached the code.

Thank you.

,rtrim(r.reltitle)+' '+RTRIM(r.relfirst)+' '+isnull(rtrim(r.relmiddle),'')+RTRIM(r.rellast) as ContactFullName

Open in new window

Question by:lcallah93
    LVL 92

    Accepted Solution

    rtrim(r.reltitle)+' '+RTRIM(r.relfirst)+isnull(rtrim(r.relmiddle) + ' ','')+RTRIM(r.rellast) as ContactFullName
    LVL 11

    Assisted Solution


    ,rtrim(r.reltitle)+' '+RTRIM(r.relfirst)+' '+case when r.relmiddle IS null then '' else r.relmiddle + ' ' end +RTRIM(r.rellast) as ContactFullName

    Author Closing Comment

    Thank you - both work beautifully!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now