0

(I use sql server 2015) In my Orders table Postal Code is a varchar.

I want to do a search like this:

SELECT distinct email
  FROM [dbo].[tb_Order] 
  where postalCode in (20004,20528,20260,20529,22060,22181,20708)

(with many more zip codes)

I do not want to have to convert the list of codes into ("20004","20528","20708").

What is the best way to handle this?

  • 1
    That's a version I do not know, SQL Server 2015 :) you can try casting it to int. But I guess the best would be to quote it. – Stijn Wynants Aug 18 '16 at 14:42
  • 3
    You should use the correct datatype and that's a VarChar, thus ('20004','20528','20260','20529','22060','22181','20708') is correct. When you use integers the optimizer must cast postalCode in every row to an numeric value, this means you will loose indexed access and probably statistics. – dnoeth Aug 18 '16 at 14:54
  • Are you actually using Sql Server 2016? – Scott Hodgin Aug 18 '16 at 14:57
  • Where are you getting your input of PostalCodes? Is it just a text list? – Arthur D Aug 18 '16 at 15:17
  • 3
    the best way to handle this is to sanitize the input using client-side code, or to do a better job modeling your data – swasheck Aug 18 '16 at 16:10
5

If you're actually using Sql Server 2016, you can probably use the new STRING_SPLIT command to create a 'table' to join against. Just put your comma delimited integers in the STRING_SPLIT command. Here is an example.

declare @tb_Order table (col1 int, postalcode varchar(5))
insert into @tb_Order values(1,'11111')
insert into @tb_Order values(2,'22222')
insert into @tb_Order values(3,'33333')
insert into @tb_Order values(4,'44444')
insert into @tb_Order values(5,'55555')
---------------------------------------
SELECT a.*
FROM @tb_Order a
JOIN STRING_SPLIT('11111,22222,33333,44444,55555',',')   
    ON convert(varchar(5),value) = a.postalcode;  
  • Nice! Was trying to see if there is a limit to STRING_SPLIT()'s input, but MSDN says "any characther type (i.e. nvarchar, varchar, nchar or char)" so nvarchar(max) should work (not tested yet by me), in case one has thousands of postal codes to compare. – Martin S. Stoller Aug 18 '16 at 16:00
2

For most Microsoft SQL versions (tested on 2005+) this will work (using Microsoft's demo "AdventureWorks" DB as a lab source):

SELECT DISTINCT [PostalCode]
FROM [Person].[Address]
WHERE
isnumeric([PostalCode]) = 1
AND [PostalCode] IN (61953, 29910, 78100);

The main issue is that we need to exclude all rows with PostalCodes that are not numeric (ie: Canadian, eh?). Otherwise we get a "Conversion failed when converting the nvarchar value 'K4B 1S2' to data type int." error in above select running on AdventureWorks.

Starting with SQL 2012, we now have TRY_PARSE():

SELECT DISTINCT [PostalCode]
FROM [Person].[Address]
WHERE 
try_parse([PostalCode] as int) IN (61953, 29910, 78100);

Performance note: Adding ISNUMERIC() or TRY_PARSE() (or any other conversion code) will cause SQL Server to inspect each and every row to see if valid. This may cause quite a performance hit on large tables.

Design note: Unless you are 100% certain you only care about numerical PostalCodes for ever, you really should consider using the same data type in the IN() as the column you are testing against.

  • 1
    You are also dealing with an implicit conversion that will slow you down even without the isnumeric/try_parse. – Kenneth Fisher Aug 18 '16 at 16:51
  • Good point, that is indeed the case in the first example using ISNUMERIC(), so a double performance hit there... – Martin S. Stoller Aug 18 '16 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.