Solved

How to prevent extra space when combining columns

Posted on 2015-01-16
11
81 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

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 14

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
 

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 14

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
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.

 
LVL 14

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:SimonAdept
SimonAdept 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:SimonAdept
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

13 Experts available now in Live!

Get 1:1 Help Now