VALID Varchar Values

Question

Post reply

VALID Varchar Values

Michelle-164472

SSCommitted

Points: 1715
More actions
October 6, 2004 at 1:43 pm

#163435

Hello All,
I have a field in one of my tables (Delivery_Area) defined as a varchar(10) and it's part of a primary key which is erroring with duplicate key issue...here is what the fields pkey data looks like? So, my question, is '-' DASH a VALID varchar value? It's as if the value is being truncated at the DASH...hope this is not too confusing
delivery_no + date + delivery_area
11 02/02/05 A
12 02/02/05 A-night
Pulled from BOL...
The char and varchar data types store data composed of:
·         Uppercase or lowercase characters such as a, b, and C.
·         Numerals such as 1, 2, or 3.
·         Special characters such as the "at" sign (@), ampersand (&amp, and exclamation point (!).
char or varchar data can be a single character, or a string with a maximum of 8,000 characters.
Thanks...Marsha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

from what you wrote, it looks like you need varchar(20) and not (10)

it truncates at the tenth char and these two would then be identical

Michelle-164472 SSCommitted Points: 1715 More actions · Answer 2

Thanks for the prompt reply but here are the datatypes of the composite PK...so, the TOTAL size of the PK is 20 + characters...

delivery_number varchar(10)

delivery_date smalldatetime

delivery_area varchar(10)

you are saying that it will ONLY use the value of varchar(10) for the PK?

Thanks...Marsha

john-132685 SSCertifiable Points: 6084 More actions · Answer 3

um, no, and I never beat my wife either.

there's nothing special about the dash (or any other character) unless it is stripped by your client process on loading. So take a close look at everything.

If you think the dash is a problem then you should be able to recreate it in a temp table with two rows and create the index on that and see if it fails. That would be a bug so huge as to have been found by almost everyone.

Michelle-164472 SSCommitted Points: 1715 More actions · Answer 4

Thanks...I wasn't able to make it fail, it had no problems with the '-' dash as a VALID varchar value...I will send this one back to the developers to take a look at their application coding.

Thanks for your assistance and advice...Marsha