Implicit typecast observed in SQL Server 2000

In my SQL Server 2K DB table there is a varchar zipcode field which happens to output incorrect character length of the zipcode when it encouters a numeric hyphenated zipcode. For example, for the value开发者_StackOverflows such as ABC12-1234 it would emit correct length i.e. 10, however when the zipcode is something like 21345-5 it emits its length as 5 instead of 7. I wonder how this implicit uncalledfor typecasting be avoided. Any ideas?

---

So your complaint is that if someone does

INSERT INTO YourTable(ZipCode) VALUES(21345-5)

i.e they omit the quotes it will get treated as

INSERT INTO YourTable(ZipCode) VALUES(21340)

and you want this to be avoided and raise an error because of the cast from int to varchar

This strict behaviour isn't possible in any version of SQL Server yet. Though it is a moderately highly voted request on the connect site.

Must all zipcodes in your locale contain a - character? If so you could enforce this aspect with a check constraint otherwise you'll just need to exercise care in your insert statements.

Any inserts coming from your application should be parameterised anyway to the correct type so I assume this does only apply to manual insert statements?

---

I can't seem to reproduce this - this snippet works flawlessly on SQL Server 2000:

CREATE TABLE ZipCodeTest(ID INT IDENTITY PRIMARY KEY, ZipCode VARCHAR(20))

INSERT INTO dbo.ZipCodeTest(ZipCode) VALUES('ABC12-1234')
INSERT INTO dbo.ZipCodeTest(ZipCode) VALUES('21345-5')
INSERT INTO dbo.ZipCodeTest(ZipCode) VALUES('ABC')
INSERT INTO dbo.ZipCodeTest(ZipCode) VALUES('32144')

Then I do a SELECT on that table:

SELECT
    ID, ZipCode, LEN(ZipCode)
FROM
    ZipCodeTest

and get the following output:

ID  ZipCode      LEN        
1   ABC12-1234    10
2   21345-5        7
3   ABC            3
4   32144          5

Your "problem case" (with ID = 2) shows LEN(ZipCode) = 7 as expected.

Can you show us

• your table structure
• your SELECT statement that returns the erroneous length information??