September 5, 2014 at 6:52 pm
Hi,
I thought it's easy but somehow got lost, what is the good way to insert that bloody Comma intelligently, depending if any of var value is NULL.
-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )
Tx
Mario
September 6, 2014 at 11:51 am
It depends what you want to do with the NULLs. You can either turn them into zero length strings, or just throw them away. Or you may wnt to do something different from either of those - your question doesn't actully explin what you want to do.
Run the first statement below to get the result with each null treated as a zero length string. Alternatively run the second statement below to get each NULL thrown away.
-- statement to get comma separated list with nulls treated as empty strings
SELECT ss = coalesce(@var1,'')+','+coalesce(@var2,'')+','+coalesce(@var3,'')+','+coalesce(@var4'');
--statement to thrown nulls away and get comma separated list of what's left
WITH GRE as (SELECT coalesce(@var1,'')+','+coalesce(@var2,'')+','+coalesce(@var3,'')+','+coalesce(@var4'') as Z),
RED = (SELECT replace(replace(Z,',,',','),',,',',') AS X from GRE),
BLU = (SELECT CASE WHEN X like ',%' then substring (X,2,len(X)) ELSE X END as Y FROM RED)
SELECT ss = CASE WHEN Y like '%,' then substring(Y,1,len(Y)-1) ELSE Y END FROM BLU) ;
If neither is what you want, post an explanation of what it is you do want. Say what you want to happen if all the strings are null, what you want to happen if none of the strings are null, say what you want to happen if the second and third strings are null but the first and fourth are not, and so on.
Tom
September 6, 2014 at 2:40 pm
mario17 (9/5/2014)
Hi,I thought it's easy but somehow got lost, what is the good way to insert that bloody Comma intelligently, depending if any of var value is NULL.
This is quite simple: Add comma in front of each but the first variable and use the CONCAT function. Then conditionally STUFF the output by the length of the first variable using ISNULL to eliminate a leading comma if the first variable is missing.
😎
Sample using variables and table columns
USE tempdb;
GO
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
/* Conditional stuff'ing if the first variable is NULL */
SELECT STUFF(CONCAT(@var1,',' + @var2,',' + @var3,',' + @var4),1,ISNULL(LEN(@var1),1),ISNULL(@var1,''))
SET @var1 = NULL;
SELECT STUFF(CONCAT(@var1,',' + @var2,',' + @var3,',' + @var4),1,ISNULL(LEN(@var1),1),ISNULL(@var1,''))
/* Sample concatinating columns
testing most combinations
*/
DECLARE @NULL_SAMLES TABLE
(
NS_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,COL_01 VARCHAR(10) NULL
,COL_02 VARCHAR(10) NULL
,COL_03 VARCHAR(10) NULL
,COL_04 VARCHAR(10) NULL
)
INSERT INTO @NULL_SAMLES(COL_01,COL_02,COL_03,COL_04)
VALUES
('A','B','C','D')
,(NULL,'B','C','D')
,('A',NULL,'C','D')
,('A','B',NULL,'D')
,('A','B','C',NULL)
,(NULL,'B',NULL,'D')
,(NULL,'B','C',NULL)
,('A',NULL,'C',NULL)
,('A',NULL,NULL,'D')
,('A',NULL,NULL,NULL)
,(NULL,'B',NULL,NULL)
,(NULL,NULL,'C',NULL)
,(NULL,NULL,NULL,'D')
SELECT
NS.COL_01
,NS.COL_02
,NS.COL_03
,NS.COL_04
,STUFF(CONCAT(NS.COL_01,',' + NS.COL_02,',' + NS.COL_03,',' + NS.COL_04),1,ISNULL(LEN(NS.COL_01),1),ISNULL(NS.COL_01,'')) AS CONC_COLS
FROM @NULL_SAMLES NS
Results
No empty variable
----------------------------------------------------------
alpha1,bravo2,charlie3,delta4
First variable empty
----------------------------------------------------------
bravo2,charlie3,delta4
COL_01 COL_02 COL_03 COL_04 CONC_COLS
---------- ---------- ---------- ---------- --------------
A B C D A,B,C,D
NULL B C D B,C,D
A NULL C D A,C,D
A B NULL D A,B,D
A B C NULL A,B,C
NULL B NULL D B,D
NULL B C NULL B,C
A NULL C NULL A,C
A NULL NULL D A,D
A NULL NULL NULL A
NULL B NULL NULL B
NULL NULL C NULL C
NULL NULL NULL D D
September 8, 2014 at 10:01 am
Thansk, mucho Tom and Eirikur.
Eirikur, that's so wonderful usage of combo CONCAT and '+'. just realized how it works. Null + 'Something' = NULL
DECLARE @var3 varchar(10) = 'charlie3', @var4 varchar(10) = 'Delta4';
/* Conditional stuff'ing if the first variable is NULL */
SELECT ConVar = CONCAT(@var3,(', ' + @var4));
SET @var4 = NULL
SELECT ConVarNull = CONCAT(@var3,(', ' + @var4)); -- no Comma afer Chalie3 goes in.
Best
Mario
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply