Odd issue with leading zero's from a varchar being dropped.

Question

Odd issue with leading zero's from a varchar being dropped.

Matthew.Procter

SSC Journeyman

Points: 76
More actions
June 8, 2014 at 9:19 pm

#307977

Hi all,
Got an issue that I can't work out. I have 2 tables, with "clientcode" field that in both is a varchar(50)
If I query the tables individually, I get the leading zero's.
If i use the below code, all the leading zero's are dropped off
Select ClientCode, MailingName from MPSR_FlattenedCDS_Data
where ClientCode not in (select CS.ClientCode from ClientSupplier as CS)
Any idea's anyone?
Thanks
Matthew

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

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Matthew.Procter (6/8/2014)
Hi all,
Got an issue that I can't work out. I have 2 tables, with "clientcode" field that in both is a varchar(50)
If I query the tables individually, I get the leading zero's.
If i use the below code, all the leading zero's are dropped off
Select ClientCode, MailingName from MPSR_FlattenedCDS_Data
where ClientCode not in (select CS.ClientCode from ClientSupplier as CS)
Any idea's anyone?
Thanks
Matthew

First thought would be an implicit type cast to a numeric data type, what is the output data type?

An other possibility is that if not all entries have leading zeros, then if all those with the leading zeros existing in both sets, this would be expected.

😎

ScottPletcher SSC Guru Points: 101201 More actions · Answer 2

If there's a NULL in the "NOT IN" list, no rows will be excluded, so you may be seeing rows -- including without leading zeros -- that you didn't expect to see.

To verify, please try this instead:

Select ClientCode, MailingName

from MPSR_FlattenedCDS_Data

where ClientCode not in (

select CS.ClientCode

from ClientSupplier as CS

where CS.ClientCode is not null )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Matthew.Procter SSC Journeyman Points: 76 More actions · Answer 3

Scott,

Tried it, but it still gives the same output. The ClientCode field doesn't allow nulls in the ClientSupplier table, and the data that MPSR_FlattenedCDS_Data was derived from didn't allow nulls either. The values are a mixture of numbers (but some have leading zero's that need to be retained), alphanumeric, and numeric with a # used as a delimiter (ie 0000123; 987654; ABC01; 123456#01)

Cheers

Matthew

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

If you move a subset of the data to a test database with tables created as they are in production, can you reproduce the issue?