Concatination Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatination Question

Author

Topic

Lina74
Starting Member

2 Posts

Posted - 2010-04-26 : 11:52:39

Hi,
I have a question about concatenating values from multiple columns into one in the SELECT statement.

Let me explain what I mean. I'll simplify:

I have 3 columns: colA, colB and colC.

When I select it like this:

SELCT (isNUll(colA,'') + ', ' + isNUll(colB,'') + ', ' + isNUll(colC,''))

If none of the columns are NULL, my result looks ok: A, B, C

The problem is that in some cases some column are NULL, in which case my result looks something like this: A, , C.

So I guess my question is how to ignore the column with a NULL values in my SELECT, so the result above would look like A, C

Thank you in advance!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-26 : 11:55:06

No idea why you'd want to do this but:
Just Move the ISNULL


SELCT isNUll(colA + ', ', '') + isNUll(colB + ', ', '') + isNUll(colC,''))

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lina74
Starting Member

2 Posts

Posted - 2010-04-26 : 12:13:07

quote:
Originally posted by Transact Charlie

No idea why you'd want to do this but:
Just Move the ISNULL

SELCT isNUll(colA + ', ', '') + isNUll(colB + ', ', '') + isNUll(colC,''))

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

This is great, Charlie! Thanks! Just one more question. Sometimes the last column is NULL. So how do I remove that last extra comma so it doesnt look like this: A, B,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-26 : 12:32:25

It's getting more horrible but maybe:


SELECT
	ISNULL(colA +
		CASE
			WHEN [colB] IS NOT NULL OR [colC] IS NOT NULL THEN ', '
			ELSE ''
		END
		, '')

	+ isNUll(colB +
		CASE
			WHEN [colC] IS NOT NULL THEN ', '
			ELSE ''
		END
		, '')

	+ isNUll(colC,'')

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 00:42:42

isnt this enough?


SELCT isNUll(colA, '') + isNUll(', ' + colB, '') + isNUll(', ' + colC,'')

this will ensure comma gets added only if you've a following value coming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-27 : 04:04:23

But if col A is NULL then the string will start with a comma if either of colb or colc are not null.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 04:22:57

oh ok...then this?


SELECT  STUFF(isNUll(', ' + colA, '') + isNUll(', ' + colB, '') + isNUll(', ' + colC,''),1,2,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-27 : 04:28:25

Well -- I think we've certainly established that there is no *nice* way.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 04:42:20

Depends on how you describe *nice*

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-27 : 04:50:26

Well...

pleasant or pleasing or agreeable in nature or appearance; NOPE it's not nice
done with delicacy and skill; Maybe but probably not.
decent: socially or conventionally correct; BLEURG -- probably shouldn't be doing this in the first place....
.
.
.
.
a city in southeastern France on the Mediterranean; -- N/A

See ya Visakh - have a good day.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources