title: MS SQL server cast on table joins
body: <p>I have a table that contains postcodes for customers, these are stored as type nvarchar and contain a mix of alphanumeric e.g. 'YO41 1LZ' and numeric e.g. 12345 postcodes. I also have a table that contains valid postcodes supplied by the courier company we use to deliver goods. Their data is stored with a postcode1 and postcode2 in each row. These values are ranges within which all the postcodes are valid e.g. postcode1 = 1234 and postcode2 = 2345 so 2321 is valid, but 1231 is not. These rows also contain alphanumeric values such as 'YO41' and are stored as type varchar.</p>
  
  <p>I want to link to this table so that when I query company details including the address, it also returns a bit flag for whether the postcode we have stored is compatible with the list the courier company has supplied. A simplified version of the query I've written is below.</p>
  
  <p>The problem is that when the postcode is numeric, I need to do a numeric comparison of the postcodes, but when alphanumeric a string comparison. The query below throws the error "Conversion failed when converting the nvarchar value 'YO41 1LZ' to data type int." I think the problem must be the way I'm using CAST. Does anyone know of a better way of doing this please? </p>
  
  <pre><code>SELECT
    company.companyname,
    address.line1,
    address.line2,
    address.postcode,
    CASE
    WHEN courierpostcodes.postcodeid IS NOT NULL
    THEN 1
    ELSE 0
    END AS postcodeValid
  FROM
    company
    JOIN address
      ON company.companyid = address.companyid
      LEFT OUTER JOIN courierpostcodes
        ON (ISNUMERIC(courierpostcodes.postcode1) = 1 AND ISNUMERIC(address.postcode) = 1
                AND CAST(address.postcode AS int) BETWEEN CAST(courierpostcodes.postcode1 AS int) AND CAST(courierpostcodes.postcode2 AS int))
  
                OR 
  
                ((ISNUMERIC(courierpostcodes.postcode1) = 0 OR ISNUMERIC(address.postcode) = 0)
                AND CAST(address.postcode AS nvarchar) BETWEEN CAST(courierpostcodes.postcode1 AS nvarchar) AND CAST(courierpostcodes.postcode2 AS nvarchar))
  WHERE
    company.id = *mycompanyid*
  </code></pre>