[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL isnull issue

Posted on 2011-10-14
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 1000 total points
ID: 36968603
rtrim(r.reltitle)+' '+RTRIM(r.relfirst)+isnull(rtrim(r.relmiddle) + ' ','')+RTRIM(r.rellast) as ContactFullName
LVL 11

Assisted Solution

rajvja earned 1000 total points
ID: 36968675

,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

ID: 36968748
Thank you - both work beautifully!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

834 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