This might do the trick. select ZIP, substring(ZIP,1,5) as shortZIP, count(substring(ZIP,1,5)) as Count from Table Group BY ZIP
Ruben -----Original Message----- From: Liam O'Brien [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2008 4:34 PM To: SQL Subject: problem with substring I am trying to limit the number of characters returned for a zip code. some are stored as 9 digits, others as 5. I am trying to get them all to return 5 digits. The zip codes are stored as nvarchar. Here is my query: SELECT ZIP, SUBSTRING(ZIP,1,5) AS shortZIP, COUNT(shortZIP) AS ZIPCount FROM table GROUP BY ZIP I would like to sum up all of the same zip codes like so: ZIP Code ZIP Code count 54098 3 20987 1 45895 7 any help would be greatly appreciated ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3081 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
