CODEWITHSUNDEEP
sqlsql-server-2005
user6363065
user6363065

Reputation: 81

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, [email protected], [email protected], [email protected], [email protected],' '','FTX_F20160607004'

I want to correct this string like 

[ftx_alert_list] 'updateLastRunDT','9017','FTX-Software, [email protected], [email protected], [email protected], [email protected]','','FTX_F20160607004'`

The only difference is ,' '', I want ','', after [email protected].

Upvotes: 1

Views: 90

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

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'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]');

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

Upvotes: 1

Related Questions