CODEWITHSUNDEEP
sqlsql-servert-sql
Rod
Rod

Reputation: 15475

tsql help with concatenting several fields

i have address columns that i would like to concatenate even if only one field is not null.

for example

street,city,state,zip
null,null,AL,36609

Currently, my sql statement is like this:

select street + ',' + city + ',' + state + ',' + 'zip as address

so the sample record above gives me null for address. is there an easy way to show whatever is not nulll?

so the example record above would return:

AL, 36609

Upvotes: 2

Views: 614

Answers (5)

Nilesh Thakkar
Nilesh Thakkar

Reputation: 2895

I'd write it as below :

DECLARE @Address VARCHAR(200)
select @Address= isnull(',' + street, '') + isnull(',' + city, '') + 
isnull(',' + state,     '') + isnull(',' + zip, '') from yourtablename where criteria
SELECT STUFF(@Address, 1, 1, '') AS Address

As already pointed out by other members, when you perform any operation on fields having NULL, resultant expression(value) would always be null.

Upvotes: 0

Oded
Oded

Reputation: 499272

You can use COALESCE:

SELECT
  COALESCE (street, '') +
  COALESCE (city, '') +
  state +
  zip AS address

An alternative is to use ISNULL:

SELECT
  ISNULL(street, '') +
  ISNULL(city, '') +
  state +
  zip AS address

Upvotes: 1

amelvin
amelvin

Reputation: 9061

declare @temp table (street varchar(100) null,city varchar(100) null, state char(2) null, zip varchar(10) null)
insert into @temp (street,city,state,zip) values (null,null,'AL','36609')

select ISNULL(street + ', ','') +  ISNULL(city + ', ','') + ISNULL(state + ', ','') + ISNULL(zip,'') from @temp

will return you

AL, 36609

EDIT

This is not pretty, but seems to work for a variety of null placements:

declare @temp table (street varchar(100) null,city varchar(100) null, state char(2) null, zip varchar(10) null)
insert into @temp (street,city,state,zip) values (null,null,'AL','36609')
insert into @temp (street,city,state,zip) values (null,null,'AL',null)
insert into @temp (street,city,state,zip) values ('Acacia Avenue',null,'AL',null)
insert into @temp (street,city,state,zip) values ('Acacia Avenue',null,null,null)

select
  case 
    when city is null and state is null and zip is null then ISNULL(street, '')
    when state is null and zip is null then ISNULL(street + ', ','') +  ISNULL(city,'')
    when zip is null then ISNULL(street + ', ','') +  ISNULL(city + ', ','') + ISNULL(state,'')
    else ISNULL(street + ', ','') +  ISNULL(city + ', ','') + ISNULL(state + ', ','') + ISNULL(zip,'') 
end 
from @temp

RESULTS

AL, 36609
AL
Acacia Avenue, AL
Acacia Avenue

(4 row(s) affected)

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Try this query:

SELECT  SUBSTRING(
     ISNULL(','+street,'')
    +ISNULL(','+city,'')
    +ISNULL(','+state,'')
    +ISNULL(','+zip,'')
    ,2, 500) AS address
FROM    table

Upvotes: 1

Paul Williams
Paul Williams

Reputation: 17040

Try this:

select
    isnull(street + ',', '') +
    isnull(city + ',', '') +
    isnull(state + ',', '') +
    isnull(zip + ',', '')

This relies on the standard NULL behavior where any operation on NULL returns NULL. So street + ',' will return NULL if street is NULL. The ISNULL operator will then choose the empty string instead.

For more information about NULL concatenation, see SET CONCAT_NULL_YIELDS_NULL. (This option will always be enabled in a future version.)

Upvotes: 4

Related Questions