Hi,
I am trying to replicate this statement in a SELECT:

IIf(IsNull([cmZip4]),[cityst] & "  " & [cmZip],[cityst] & "  " & [cmZip] &
"-" & [cmZip4]) AS CityStZip

If cmZip4 is null, I want to format cityst + " " + cmzip, if it is not null,
I want to format it as cityst + "" cmZip + "-" + cmZip4

In my SELECT, I want to fomat this AS cityStzip. 

I am not having much luck, any thoughts or help is appriciated!

Thanks,
Steve

In a tiered archtecture, format is never done in the database; that is
a front end job.

Try,

select
    coalesce([cityst] + "  " + [cmZip] + "-" + [cmZip4], [cityst] + "  " +
[cmZip]) as CityStZip
....


AMB

Show quoteHide quote

Hope this helps.
/*
IIf(IsNull([cmZip4]),[cityst] & "  " & [cmZip],[cityst] & "  " & [cmZip] &
"-" & [cmZip4]) AS CityStZip

If cmZip4 is null, I want to format cityst + " " + cmzip, if it is not null,
I want to format it as cityst + "" cmZip + "-" + cmZip4

In my SELECT, I want to fomat this AS cityStzip. 
*/

Create table #Address
(
cityst varchar(20),
cmZip  varchar(5),
cmZip4 varchar(4)
)

Insert #address
values ('Dallas, Tx','75229',Null)


Insert #address
values ('Plano, Tx','75252','1234')

SELECT CASE WHEN cmZip4 IS NULL
            THEN cityst + ' ' + cmZip
            WHEN cmZip4 IS NOT NULL
            THEN cityst + ' ' + cmZip +'-'+ cmZip4
       END
    FROM #address

--output

Show quoteHide quote

View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
3 Questions on db Theory/Design
restoring db without log