Solved

concatenate 2 columns into text field (tsql)

Posted on 2011-09-22
32
497 Views
Last Modified: 2012-05-12
I want to concatenate 2 columns into a text field

if column1 is not null, i want it in the first line and then column2 should start in second line. if column1 is null or empty, then column2 should start in first line of text field..

how would you do it?
0
Comment
Question by:25112
  • 14
  • 12
  • 6
32 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 36584441
>>I want to concatenate 2 columns into a text field<<
Do you mean you are still using the text data type?

>>if column1 is not null, i want it in the first line and then column2 should start in second line.<<
There is no concept in SQL Server for lines.  Do you mean the output should be delimitted by Cr or Lf or CrLf or some other delimitter?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36584442
25112,

Have a look at COALESCE() or ISNULL(). If you actually want lines, then look at CHAR(13)+CHAR(10) which is carriage return and line feed. In other words, with columns A and B, I can do something like this:

COALESCE(A+CHAR(13)+CHAR(10), '') + COALESCE(B+CHAR(13)+CHAR(10), '')

It is up to you if the last column -- B in this example -- should have the carriage return and line feed after it or not.
0
 
LVL 5

Author Comment

by:25112
ID: 36584460
>>I want to concatenate 2 columns into a text field<<
Do you mean you are still using the text data type?
Yes- this is custom package.. it has lots of them..

>>if column1 is not null, i want it in the first line and then column2 should start in second line.<<
There is no concept in SQL Server for lines.  Do you mean the output should be delimitted by Cr or Lf or CrLf or some other delimitter?

example
column1 starts here
column2 starts here if column1 is not null...
0
 
LVL 5

Author Comment

by:25112
ID: 36584466
mwvisa1, i just looked up COALESCE- it says it is like CASE..

I think I can stop at
COALESCE(A+CHAR(13)+CHAR(10), '') + B

this means if A is NULL, then only B will be returned, right?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36584472
You can put in the carriage return and line feed characters as I showed, but do not expect to see that looking at the data directly. That is really for results/presentation.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36584480
Yes, if you can guarantee that B is never NULL, then it can be simplified to what you have. You are correct. I like using COALESCE() because it is more portable. In this situation, you really are using it in the simple form which is like ISNULL(). COALESCE() is a little more powerful as it can take multiple parameters and pass back the first non-null value. CASE allows conditions, so is a little different, but I guess if you where doing CASE WHEN A IS NOT NULL THEN A ... type logic then it would be equivalent to say COALESCE(A, ...) which does the WHEN A IS NOT NULL THEN A bit in one action.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36584565
>>Yes- this is custom package.. it has lots of them..<<
If they really are text data types, then you are going to have a lot of problems as you cannot simply concatenate them, you will have to use something like the UPDATETEXT() function.

>>example
column1 starts here
column2 starts here if column1 is not null... <<
Unfortunately, that tells me nothing.  Possibly CrLf.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36584618
That is how I am interpreting that, Anthony, but then again I read "text field" to mean "text area" as in display and not "text column." Therefore, I would not trust my reading skills. ;)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36586476
>>but then again I read "text field" to mean "text area" as in display and not "text column." <<
And I am sure you are right, that is why I am asking twice to make sure.  There is a big differnce between varchar and text and the author needs to clarify as the answer is very different in either case.
0
 
LVL 5

Author Comment

by:25112
ID: 36586491


mwvisa1,
>>You can put in the carriage return and line feed characters as I showed, but do not expect to see that looking at the data directly. That is really for results/presentation.

good point.. i can't see it in SSMS.. i am sure though it will show up in the GUI screen OK.

>>Yes, if you can guarantee that B is never NULL,
thanks for pointing it out.. oops.. no, we cannot guarentee it.. so i will have to use your full syntax suggestion..

also, to confirm (sorry i did not make crystal clear) , yes, as I mentioned .. column2 is text and column1 varchar... the result is going to a text field.. (for GUI viewing.. more columns will be available to display them)... is COALESCE a good choice for text columns? (in terms of performance)

actually my manager gave me the previous code that the old programmer wrote, but never implemented thinking it is performance concern..

case when Column1 IS NOT NULL AND LEN(cast(Column1 as varchar(max))) > 0 THEN Column1+space(10)+
cast(case when LEN(cast(notes.Column2 as varchar(max))) = 0 or notes.Column2 is null then '' else notes.Column2 end AS varchar(max))
else case when LEN(cast(notes.Column2 as varchar(max))) = 0 or notes.Column2 is null then '' else notes.Column2 end end Comment,

could you suggest your input related to perforamnce in relation to this above query, as they were concern about too much casting for millions of records.
0
 
LVL 5

Author Comment

by:25112
ID: 36586496
acperkins,

>>If they really are text data types, then you are going to have a lot of problems as you cannot simply concatenate them, you will have to use something like the UPDATETEXT() function.

thanks for the warning.. maybe i will put a word to the management.. so they can thinking about it in talking to vendor about updates etc..

is UPDATETEXT() equivalent to cast(textfield as varchar(x))? in this context..

>>CrLf
is that same as char(10)+char(13)?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36588678
If Column2 is TEXT, then the CAST/CONVERT is necessary to be able to concatenate it with a VARCHAR. Your alternative would be to use TEXT appropriate function as Anthony pointed out; however, given TEXT is going the way of the dodo ... the functions like UPDATETEXT, for obvious reasons, will be gone too. So maybe less efficient, but might save code re-write later if the column type is changed.

CREATE TABLE #T(A VARCHAR(200), B TEXT);
INSERT INTO #T(A, B)
VALUES('ABC', 'XYZ'),(NULL, 'DEF');

SELECT COALESCE(A+CHAR(13)+CHAR(10), '') 
     + COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')
FROM #T;

DROP TABLE #T;

Open in new window


As far as the code itself, it is using SPACE(10) which suggests it is more of a fixed width scenario than carriage return / line feed, so if the requirement is the latter the original code did not meet it. Yes, that is what char(13)+char(10) is meant to give you, but note the order. :) Additionally, I would not call UPDATETEXT equivalent to the cast. It is for leaving the TEXT as TEXT and updating it via pointer.

Aside from all that, the CASE method of convoluted. If you need to check for '' also, then just do this:
COALESCE(NULLIF(A, '')+CHAR(13)+CHAR(10), '')
You can always add LTRIM() in case you have ' ' or similar all spaces string.

Further, even if you do use CASE, I would do:
CASE WHEN A <> '' THEN A + SPACE(10) ELSE '' END + CAST(B AS VARCHAR(MAX))

It is a bit redundant. Anyway, hope all this is helping.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590661
>>the functions like UPDATETEXT, for obvious reasons, will be gone too. <<
Very true.  But if the author is still using SQL Server 2000 then varchar(MAX) is not an option.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590666
And even if they are using SQL Server 2005/2008/2008 R2 and Compatibility Level is still set to 80 they will still be up the creek using varchar(MAX)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36590703
True. I was going on the fact that http:#36586491 contained VARCHAR(MAX). You are absolutely right, though.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590717
Ah, good point, I missed that.  Now why in the world are they still using the deprecated text data type ... ?  <purely rhetorical>
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:25112
ID: 36592837
I am following you.. Yes, we put a question to vendor why they still use text in sql 2008?

0
 
LVL 5

Author Comment

by:25112
ID: 36592932
the logic is very simple and clearer than the CASE.. also space(10) does not meet requirement as you said..

SELECT len(COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')),
               COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')

or

SELECT datalength(COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')),
               COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')

both gives something like this..

even if blank output, len gives some value.. why could that be.. it is same as datalength

(No column name)	(No column name)
77	                                                                             
2	  
2	  
77	                                                                             
77	                                                                             
2	  
2	  
2	  
77	                                                                             
2	  
43	  moved to Georgetown NC  61 Westland Way  
2	  
2

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36593271
You probably have empty strings which then get two characters CHAR(13)+CHAR(10) added, explaining the 2. For the others, you probably have strings like '          '. Try LTRIM() or RTRIM() arund the columns like LTRIM(A) or RTRIM(CONVERT(VARCHAR(MAX), B)) to get rid of empty spaces...

Note: the choice of Left Trim or Right Trim was totally arbitrary there. If you have a string of all strings, it does not matter. As a point of practicality, usually strings have trailing blank space to fill the size of the data type; therefore, RTRIM() is probably a good one to go with even though in this scenario it does not matter.

Now that you have just '' (empty string), you can force it to NULL using NULLIF. So in total.

COALESCE(NULLIF(RTRIM(A), '')+CHAR(13)+CHAR(10), '')
0
 
LVL 5

Author Comment

by:25112
ID: 36593310
when i run ltrim, rtrim

SELECT
A,
B,
len(ltrim(rtrim(COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')))),
len(COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')),
    COALESCE(A+CHAR(13)+CHAR(10), '')+ COALESCE(CONVERT(VARCHAR(MAX), B)+CHAR(13)+CHAR(10), '')

i get the below..are the 2 spaces char(13)+char(10)..  is it possible to clean them up after the above statement if the resulting concatenation is blank?


Untitled.jpg
0
 
LVL 5

Author Comment

by:25112
ID: 36593313
i just saw your other post.. yes, nulling them would be a good idea, too...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36593320
I would go with that. It avoids having to do the checks later. Do it on both sides as ''+'' will yield '' and have no data length. What you are seeing now is one of the sides is NOT NULL, so you are getting the two character lengths for carriage return/line feed combination.
0
 
LVL 5

Author Comment

by:25112
ID: 36593326
NULLIF does not show it as NULL, right? just makes it 0 length?
0
 
LVL 5

Author Comment

by:25112
ID: 36593333

With the CASE, I saw 95% Clustered Index Scan, 2% Merge Join, 3% Filter.
With COALESCE, I saw 97% Clustered Index Scan, 2% Merge Join, 1% Filter.

with millions of records, this is a marginal but still a positive improvement in performance, right?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36593341
NULLIF(X, Y) forces X to NULL when it is equal to Y; therefore, NULLIF([A], '') returns NULL if column [A] is an empty string. To account for variable spaces, that is why we RTRIM() first -- NULLIF(RTRIM([A]), '') will return NULL if the string is null, empty, or only contains spaces. NULL + {anything} = NULL; therefore, the CHAR(13)+CHAR(10) will not count unless we have a valid non-null string. :)

Our end result does not have NULLs because we wrap the whole thing with COALESCE() or ISNULL() which then converts this back to ''. It is just a shorter hand form, instead of having to use CASE syntax to do all the checking for all the exceptional cases.
0
 
LVL 5

Author Comment

by:25112
ID: 36593345
>>therefore, the CHAR(13)+CHAR(10) will not count unless we have a valid non-null string. :)

beautiful... thanks..
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36593367
The key to get relative performance is seeing how the two query plans relate to each other, i.e., put them in one batch and get execution plans and it should show you the relative cost in the batch for each. As these are in the SELECT, I would see about doing something in a WHERE clause to impact performance.

0
 
LVL 5

Author Comment

by:25112
ID: 36593416
WHERE conditions being the same, I used estimated plan and option and saw the statistics as on # 36593333..

i tried statistics io, and lob logical reads for NULLIF option is little higher, but probably worth it..

Cheapest is COALESCE Alone
second cheapest is CASE Format
Third is COALESCE+RTRIM
Fouth is COALESCE+RTRIM+NULLIF
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36593545
Nice. But as you said, you may end up having to sacrifice for the benefits of the RTRIM+NULLIF combination to output. In other words, if you implement the same functionality with CASE to handle strings of all spaces and not get the additional characters in output when string is empty, then may equalize the cost. Plus as I was told by Microsoft, io and logical reads are good indicators but are not definitive judges of good versus bad performance. CPU is another, etc. If performance is not too hateful on any of the four, go with whichever fits your needs the best. :)
0
 
LVL 5

Author Comment

by:25112
ID: 36594116
that is sage advise.. thanks.. there is no simple way to check the CPU for it, is there (like for Logical reads)
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 450 total points
ID: 36594120
-- http://msdn.microsoft.com/en-us/library/ms190287.aspx
SET STATISTICS TIME ON;
GO
/* your query or queries. */
GO
SET STATISTICS TIME OFF;
GO

You can look at the execution plan also, as I said the relative cost may be interesting.
http://msdn.microsoft.com/en-us/library/ms176107.aspx
0
 
LVL 5

Author Comment

by:25112
ID: 36594133
you truly know a lot.. thanks for sharing!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

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