SQLTeam.com | Weblogs | Forums

TSQL Conditional LEFT/RIGHT


#1

I have a column in a table that is called ZC for ZipCode. It has trailing zeros if the ZipCode does not have the Plus4.

Sample Data:
902100000
902100000
902101245
902108591

Desired Output:
90210
90210
90210-1245
90210-8591

This is the TSQL I have now, but it isn't correct:
LEFT([ZC],5) AS ZIP
,RIGHT([ZC],4) AS PLUS4
,LEFT([ZC], 5) + '-' + RIGHT([ZC],4) AS TESTZIPPLUS4

 /* ,IF (RIGHT([ZC],4)) <> '0000'
		{
		LEFT([ZC], 5) + '-' + RIGHT([ZC],4) AS ZIPPLUS4}
		} [ELSE
		{LEFT([ZC], 5) AS ZIPPLUS4} ]

Can I conditionally do an IF to format this data so it appears the way I want it to?

Thank you


#2

SELECT zc, CASE WHEN RIGHT(zc, 4) = '0000' THEN LEFT(zc, 5) ELSE STUFF(zc, 6, 0, '-') END AS zipplus4


#3

That worked! Thank you!