Solved

How to prevent extra space when combining columns

Posted on 2015-01-16
11
89 Views
Last Modified: 2015-01-17
Dear Experts,

I have the following columns with corresponding results:

FirstName - Not Null
SecondName - Allow Null
ThirdName - Allow Null
UniName- Allow Null  
UniName- Not Null

with 3 Names - this is ideal:
Jose Maria Carlos Cruz

With Single Name only - having 2 extra spaces:
Jose   Cruz

With 2 Names only - having extra space:
Jose Maria  Cruz

This happen when I use IsNull function. Is there a better to do this?

SELECT   FirstName + ' ' + ISNULL(SecondName, '') + ' ' + ISNULL(ThirdName, '') + ' ' + ISNULL(UniName, '') + LastName AS FullName


Thanks.
0
Comment
Question by:JimiJ13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40554919
Hello,

Please try this query

SELECT   FirstName + ISNULL(' ' + SecondName, '') + ISNULL(' ' + ThirdName, '') +  ISNULL(' ' + UniName, '') + LastName AS FullName

Open in new window

0
 

Author Comment

by:JimiJ13
ID: 40554947
Thanks Vikas for the prompt reply. However, the result is the same.
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40554986
Hello,

Then there will be issue with the data

you have to Trim the data to remove spaces
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:JimiJ13
ID: 40555028
There is no issue with the data - I am working on the sample and make sure there is no extra space on them.  
There is a space being added for every name and the space remained for every null name and that's the issue that I am asking to be fixed.

Have you tried solution?
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40555034
As I told that you are adding space before checking isnull so if the value is null then the space remains

But if you first add space to column and then check for isnull then that extra space will not come.
0
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 300 total points
ID: 40555035
It might be the case that in the column there is no null but just blank then also the space will be added


I think you should try

CASE when len(col) > 0 then ' ' + col else ''
for your logic
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 200 total points
ID: 40555114
I'd suggest that you just wrap your string with REPLACE(YourConstructedStringHere,'  ',' ') to strip multiple spaces.

SELECT  
REPLACE(FirstName + ' ' + ISNULL(SecondName, '') + ' ' + ISNULL(ThirdName, '') + ' ' + ISNULL(UniName, '') + ' ' +  LastName ,'  ',' ') AS FullName
from YourTableName

Open in new window


REPLACE works through the string and replaces ALL runs of multiple spaces with a single space, regardless of the number of consecutive spaces.
0
 

Author Comment

by:JimiJ13
ID: 40555637
Thank you guys for the ideas. However, I  opted to use IIF and handle both nulls and zero lengths as follows:

SELECT  FirstName +  IIF(ISNULL(SecondName,'a')='a' OR LEN(SecondName)<1,'',' '+SecondName) + IIF(ISNULL(ThirdName,'a')='a' OR LEN(ThirdName)<1 ,'',' '+ThirdName) + IIF(ISNULL(UniName,'a')='a' OR LEN(UniName)<1, '',' '+UniName) + ' ' +  LastName AS FullName
from Table

I would appreciate if you can offer a better option.

BTW, there is a comparison between IIF and Case here:
http://beyondrelational.com/modules/2/blogs/70/posts/19593/performance-comparison-iif-logical-function-vs-case-expression.aspx

Also Replace does not work on both Zero length and Null as with the previous solutions offered.

Thanks.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40555673
From the blog you referred to "IIF is supported from version 2012 onwards." - you posted in the MSSQL2008 topic area?

MSDN reference for IIF also states it is not supported in 2008 http://msdn.microsoft.com/en-GB/library/hh213574.aspx

I suggested the REPLACE function as a wrapper to act on the result of the string construction. I don't understand why you say it doesn't work (if it is used at the correct point in the process).
0
 

Author Comment

by:JimiJ13
ID: 40555922
Thanks for your comments SimonAdept.

I think Experts-Exchange must include MS SQL 2012 and 2014 in their topic selection.

The script you gave me with REPLACE function did not work. The ISNull can deal only with NULLs but no idea about EMPTY columns.  If your solution works, I will not waste time doing my own. I post my problem because I want to save time.

Anyway, although your posts and Vikas did not directly answer my issue but somehow helped me solved it.    

Thanks both of you and I want to close this topic, but expect to comeback for more help.
0
 

Author Closing Comment

by:JimiJ13
ID: 40555923
Thank you folks for the helps!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

630 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