TAGS :Viewed: 9 - Published at: a few seconds ago

[ SQL COALESCE modification ]

I have below code

SELECT @email = COALESCE(@email + ', ', '') + Email
FROM dbo.FTX_ALERTUSER WITH (NOLOCK)
WHERE AlertID = 9017 AND Email IS NOT NULL

SELECT @email2 = VENDOREMAIL 
FROM FTX_NCMR_VENDOR_REQUIREMENT WITH(NOLOCK) 
WHERE VENDORCODE = @param1 AND BU = @param2

SELECT @email = @email + ',' + ISNULL(@email2,''' ''')

Which is creating string

[ftx_alert_list] 'updateLastRunDT','9017','FTX-Software, Karthik@XYZ.com, ray@XYZ.com, tony@XYZ.com, yen@XYZ.com,' '','FTX_F20160607004'

I want to correct this string like

[ftx_alert_list] 'updateLastRunDT','9017','FTX-Software, Karthik@XYZ.com, ray@XYZ.com, tony@XYZ.com, yen@XYZ.com','','FTX_F20160607004'`

The only difference is ,' '', I want ','', after yen@XYZ.com.

Answer 1


Could you change the last line as below. Using SUBSTRING you can get your expected characters only.

SELECT @email = '''' + SUBSTRING(@email, 0, LEN(@email)) + '''' + ',' + ISNULL(@email2,''' ''')

Sample execution with the given data.

DECLARE @FTX_ALERTUSER TABLE(Email VARCHAR (50));

INSERT INTO @FTX_ALERTUSER (Email) VALUES
('FTX-Software'), ('Karthik@XYZ.com'), ('ray@XYZ.com'), ('tony@XYZ.com'), ('yen@XYZ.com');

DECLARE @FTX_NCMR_VENDOR_REQUIREMENT TABLE (VENDOREMAIL VARCHAR (50));

INSERT INTO @FTX_NCMR_VENDOR_REQUIREMENT (VENDOREMAIL) VALUES (NULL);

DECLARE @email AS VARCHAR (MAX) = '';
DECLARE @email2 AS VARCHAR (MAX) = '';

SELECT @email = @email + COALESCE(Email + ', ', '') 
FROM @FTX_ALERTUSER

--SELECT @email

SELECT @email2 = VENDOREMAIL 
FROM @FTX_NCMR_VENDOR_REQUIREMENT 

--SELECT @email2

SELECT @email = '''' + SUBSTRING(@email, 0, LEN(@email)) + '''' + ',' + ISNULL(@email2, ''' ''')

SELECT @email