# concat + isnull

• 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

• 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;`

If you aren't happy single, you won't be happy in a relationship.

Remember, happiness comes from guitars, not relationships.

• 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

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

If you aren't happy single, you won't be happy in a relationship.

Remember, happiness comes from guitars, not relationships.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

If you aren't happy single, you won't be happy in a relationship.

Remember, happiness comes from guitars, not relationships.

• 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

• 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

• 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

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