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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

concatenate 2 columns into text field (tsql)

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
25112
Asked:
25112
  • 14
  • 12
  • 6
2 Solutions
 
Anthony PerkinsCommented:
>>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
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
>>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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
25112Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
25112Author Commented:


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
 
25112Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
True. I was going on the fact that http:#36586491 contained VARCHAR(MAX). You are absolutely right, though.
0
 
Anthony PerkinsCommented:
Ah, good point, I missed that.  Now why in the world are they still using the deprecated text data type ... ?  <purely rhetorical>
0
 
25112Author Commented:
I am following you.. Yes, we put a question to vendor why they still use text in sql 2008?

0
 
25112Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
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
 
25112Author Commented:
i just saw your other post.. yes, nulling them would be a good idea, too...
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
NULLIF does not show it as NULL, right? just makes it 0 length?
0
 
25112Author Commented:

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
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
>>therefore, the CHAR(13)+CHAR(10) will not count unless we have a valid non-null string. :)

beautiful... thanks..
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
25112Author Commented:
that is sage advise.. thanks.. there is no simple way to check the CPU for it, is there (like for Logical reads)
0
 
Kevin CrossChief Technology OfficerCommented:
-- 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
 
25112Author Commented:
you truly know a lot.. thanks for sharing!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 14
  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now