concat + isnull

Question

concat + isnull

abdalah.mehdoini

Ten Centuries

Points: 1012
More actions
December 8, 2021 at 11:07 am

#3961626
hello
How to get output below query ple any one Suggest me.
i will try below
```
SELECT *,CONCAT (Column1+'|',Column2+'|',Column3 ) AS ResultFROM tblCalculation
```
but result be
A|B|C
A|B|
A|
A|C
B|C
C
B|

thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Here is a quick solution. There will be more elegant ways, I'm sure:

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
    Col1 CHAR(1) NULL
   ,Col2 CHAR(1) NULL
   ,Col3 CHAR(1) NULL
);

INSERT #t
(
    Col1
   ,Col2
   ,Col3
)
VALUES
('A', 'B', 'C')
,('A', 'B', NULL)
,('A', NULL, NULL)
,('A', NULL, 'C')
,(NULL, 'B', NULL);

SELECT t.Col1
      ,t.Col2
      ,t.Col3
      ,output = REPLACE(REPLACE(TRIM(CONCAT(t.Col1, ' ', t.Col2, ' ', t.Col3)), '  ', ' '), ' ', '|')
FROM #t t;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

CONCAT_WS() "concat with separator" ignores the nulls

select concat_ws(' | ', t.Col1, t.Col2, t.Col3) [output]
from #t t;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Steve Collins wrote:

CONCAT_WS() "concat with separator" ignores the nulls
select concat_ws(' | ', t.Col1, t.Col2, t.Col3) [output]
from #t t;

Wow, never even seen that function before, cool stuff, Steve.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

If you missed that about CONCAT_WS, you may have missed another fantastic enhancement they came out with 2017. The TRIM function. Most people give it the "yawn" because they think that it's a replacement for the LTRIM(RTRIM)) combination of removing both leading a trailing spaces. It's actually quite a bit more... you can tell it which characters to use and it will only remove them if they are leading or trailing. Embedded characters will not be affected.

2017 incorporated some really cool new features including a setting to use real CSV during BULK INSERTs and more. Here's the link to the "What's new for 2017".

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017?view=sql-server-ver15

Phil, you know me and so you'll also know why I'm looking forward to using 2019+... they have a new xp_Delete_Files and xp_Copy_Files that work on things other than just backup files! Although both are "unsupported", I'm doing bloody handsprings!

Now, if they'd just add a machine language sequence generator to replace fnTally, create a BULK EXPORT, and create a truly useful xp_Dir (the current xp_DirTree is a PITA), I'd have it made. Oh...and if they finally made PIVOT work as well as it does in ACCESS and bring back a safe version of sp_MakeWebTask and something to generate the T-SQL for an index, I'd be in 7th heaven!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Yup, I was one of the 'most people' crowd, thanks for alerting me to that, Jeff. Always good to be aware of these extra possibilities. It was also only recently that I learned about STRING_AGG() WITHIN GROUP() which is another useful possibility.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

Concat_ws was originally in MySQL then the other db's borrowed it iirc. The big news for me is .NET 6 which I'm really liking a lot because it nicely untangles the data access pipeline even more

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

Jeff Moden wrote:

Now, if they'd just add a machine language sequence generator to replace fnTally, create a BULK EXPORT, and create a truly useful xp_Dir...

The DIY way could be to use the CLI afaik. I'm sure you've considered it. We've been using Azure SQL which doesn't allow custom CLI functions. Virtual Managed Instances do permit CLI but, you know, we're unfortunately not running on that.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 8

Phil Parkin wrote:

Yup, I was one of the 'most people' crowd, thanks for alerting me to that, Jeff. Always good to be aware of these extra possibilities. It was also only recently that I learned about STRING_AGG() WITHIN GROUP() which is another useful possibility.

In most cases I've run into when STRING_AGG is appropriate then WITHIN GROUP(ORDER BY ...) is strictly necessary too. I found this out because I left it out all over the place in my own projects 🙂 Did not know that about TRIM either

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können