SQL Server - Isnull Question on SQL Statement

Hi,

I am haven problem with sql statement, were I am trying to concatenate property address into one string so I can import it into another program that has to be in this formart.

Problem is with TRANS.UNIT_NUM being null value, if there is value in TRANS.UNIT_NUM it work just fine but if its null then it doesnt return anything, I tired to do Isnull but still doesnt work properly.

Problem in statement:

CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) + ' ' END +

Full statement:

-- PROPERTY ADDRESS
 RTRIM(TRANS.ADDRESS_NUM) + ' ' + RTRIM(TRANS.ADDRESS) + ' ' +
  CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) + ' ' END +
  RTRIM(TRANS.CITY) + ', ' + RTRIM(TRANS.STATE) + ' ' + RTRIM(TRANS.ZIP_CODE)
       AS PropertyAddress,

Thanks again.

In your CASE statement, you have to provide an ELSE part because without it, it will return NULL:

CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) + ' ' ELSE '' END

Hi Ronald,

That worked but now I have another question.

When I updated the code and query the database I got the results I was looking for but one small little problem. I now have extra space in my string and I cant get rid of it. If UNIT_NUM had value everything works fine but if there is no UNIT_NUM adds extra space in my concatenate string.

UNIT_NUM no value:

10170 Kermode Court("Double Space")LAS VEGAS, NV 89178

UNIT_NUM  value:

7570 West Flamingo Avenue #255 LAS VEGAS, NV 89147

Code:

-- PROPERTY ADDRESS
 RTRIM(TRANS.ADDRESS_NUM) + ' ' + RTRIM(TRANS.ADDRESS) + ' ' +
  CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) ELSE '' END +' '+
  RTRIM(TRANS.CITY) + ', ' + RTRIM(TRANS.STATE) + ' ' + RTRIM(TRANS.ZIP_CODE)
       AS PropertyAddress,

Thanks for the help again...

Try this one:

RTRIM(TRANS.ADDRESS_NUM) + ' ' + RTRIM(TRANS.ADDRESS) + ' ' +
  CASE WHEN ISNULL(TRANS.UNIT_NUM, '') <> '' THEN '#' + RTRIM(TRANS.UNIT_NUM) + ' ' ELSE '' END +
  RTRIM(TRANS.CITY) + ', ' + RTRIM(TRANS.STATE) + ' ' + RTRIM(TRANS.ZIP_CODE)
       AS PropertyAddress

http://www.sql-server-helper.com/forums/default.asp

Hi Ronald,

That fixed my problem.

Thanks...

isnull works in two ways in sql.
when we use isNull(A,B) then we have to check column A for null if yes then set it to B.
and in second case as query we have to use like select * from table where column is null.