Coalesce SQL Server
by
·
Join the DZone community and get the full member experience.
Join For FreeCOALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value
SELECT FIRSTNAME + ' ' +COALESCE(MIDDLENAME,'')+ ' ' +COALESCE(LASTNAME,'') AS 'FULL NAME',
FIRSTNAME,
MIDDLENAME,
LASTNAME
FROM CONTACT
FULL NAME FIRSTNAME MIDDLENAME LASTNAME
-------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Syed Abbas Syed NULL Abbas
Catherine R. Abel Catherine R. Abel
Kim Abercrombie Kim NULL Abercrombie
Humberto Acevedo Humberto NULL Acevedo
Pilar Ackerman Pilar NULL Ackerman
Frances B. Adams Frances B. Adams
Margaret J. Adams Margaret J. Adams
Carla J. Adams Carla J. Adams
Jay Adams Jay NULL Adams
Ronald L. Adina Ronald L. Adina
Comma separated list
declare @emp varchar(100);
select top 10 * from employee order by employeeid
select top 10 @emp = coalesce(@emp + ', ', '') + cast(employeeid as varchar(5)) from employee order by employeeid
select @emp
EmployeeID FirstName AddressID ShiftID LastName MiddleName NameStyle NationalIDNumber Title HireDate BirthDate LoginID EmailAddress DepartmentID MaritalStatus EmergencyContactName EmergencyContactPhone SalariedFlag Gender ManagerID PayFrequency BaseRate VacationHours SickLeaveHours SalesPersonFlag CurrentFlag ModifiedDate rowguid
----------- -------------------------------------------------- ----------- ------- -------------------------------------------------- -------------------------------------------------- --------- ---------------- -------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------ ------------- -------------------------------------------------- ------------------------- ------------ ------ ----------- ------------ --------------------- ------------- -------------- --------------- ----------- ------------------------------------------------------ ------------------------------------
1 Terri 1 1 Duffy Lee 0 245797967 VP Engineering 1998-03-03 00:00:00.000 1961-09-01 00:00:00.000 adventure-works\Terri Terri@adventure-works.com 1 S Tad Orman 586-883-8338 1 F 300 2 63.4615 1 20 0 1 2003-01-15 19:26:13.900 1B76E019-0D60-4D48-B405-C9144FB9B3AB
2 Jian Shuo 2 1 Wang NULL 0 509647174 Engineering Manager 1997-12-12 00:00:00.000 1964-12-13 00:00:00.000 adventure-works\Jian Jian@adventure-works.com 1 M Qiang Wang 249-433-7659 1 M 1 2 43.2692 2 21 0 1 2003-01-15 19:26:13.900 69C8C27C-87DF-45B4-9A46-AB603268AB1B
3 Michael 3 1 Sullivan I 0 42487730 Sr. Design Engineer 2001-01-30 00:00:00.000 1969-07-17 00:00:00.000 adventure-works\Michael6 Michael6@adventure-works.com 1 S Andy Teal 766-064-3448 1 M 2 2 36.0577 3 21 0 1 2003-01-15 19:26:13.900 B219E6A5-322A-4FB5-AEEC-2541F9E8EBAA
4 Sharon 4 1 Salavaria B 0 56920285 Design Engineer 2001-02-18 00:00:00.000 1951-06-03 00:00:00.000 adventure-works\Sharon1 Sharon1@adventure-works.com 1 M Jeff Pike 870-847-2433 1 F 2 2 32.6923 4 22 0 1 2003-01-15 19:26:13.900 5869AD03-A3B3-4B3D-8E23-063E1BECEB76
5 Gail 5 1 Erickson A 0 695256908 Design Engineer 1998-02-06 00:00:00.000 1942-10-29 00:00:00.000 adventure-works\Gail Gail@adventure-works.com 1 M Gregory Erickson 474-111-9969 1 F 2 2 32.6923 5 22 0 1 2003-01-15 19:26:13.900 667D7156-8356-4F3A-84ED-2C84571FBDC9
6 Jossef 6 1 Goldberg H 0 998320692 Design Engineer 1998-02-24 00:00:00.000 1949-04-11 00:00:00.000 adventure-works\Jossef Jossef@adventure-works.com 1 M Kathie Flood 339-955-0789 1 F 2 2 32.6923 6 23 0 1 2003-01-15 19:26:13.900 153E1CB7-5AF4-4C43-AA57-1B6014B2B0C8
7 Ovidiu 7 1 Crãcium V 0 974026903 Sr. Tool Designer 2001-01-05 00:00:00.000 1968-02-18 00:00:00.000 adventure-works\Ovidiu Ovidiu@adventure-works.com 2 S Luca Dellamore 565-356-8092 0 F 2 2 28.8462 7 23 0 1 2003-01-15 19:26:13.900 EBF6EC63-AFBC-4FD0-BBD8-79FDFFC92420
8 Janice 8 1 Galvin M 0 486228782 Tool Designer 2001-01-23 00:00:00.000 1979-06-29 00:00:00.000 adventure-works\Janice Janice@adventure-works.com 2 M Walter Cavestany 171-234-7963 0 F 7 2 25.0000 8 24 0 1 2003-01-15 19:26:13.900 A9A8ECCF-6058-4112-8E62-6D8A7B8CF01D
9 Thierry 9 1 D'Hers B 0 480168528 Tool Designer 1998-01-11 00:00:00.000 1949-08-29 00:00:00.000 adventure-works\Thierry Thierry@adventure-works.com 2 M Beverly Baker 214-368-8065 0 M 7 2 25.0000 9 24 0 1 2003-01-15 19:26:13.900 CF5A6683-DD71-4EA1-9EDF-E2D85F03D1BD
10 Brian 10 1 Welcker S 0 112432117 VP Sales 2001-03-18 00:00:00.000 1967-07-08 00:00:00.000 adventure-works\Brian4 Brian4@adventure-works.com 3 S Erin Hagens 203-175-2141 1 M 299 2 72.1154 10 25 0 1 2003-01-15 19:26:13.900 39ADABF3-6A6A-4EB0-BCEB-8FF834F4DE24
(10 row(s) affected)
----------------------------------------------------------------------------------------------------
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
(1 row(s) affected)
sql
Opinions expressed by DZone contributors are their own.
Comments