asked on

Vb.Net - SQL Query Accounting for Leading Zeroes

Good Day Experts!

I have quite the quandary with my SQL Query. It "feels" like there should be a solution but I am not sure how to get there. I am looking for duplicate [Pro Number]'s across [Processing Table]'s. My current query accounts for exact match [Pro Number]'s. I have just been informed that I must account for potential leading zeroes in the target and checking [Processing Table].

So, when I compare (A2.[Pro Number] = A1.[Pro Number]) how can I also say to first strip off the leading zeroes on both sides and compare? Reason I say both sides is I will not know ahead of time which side has leading zeroes.

I know this sounds a bit confusing, please ask questions so I explain better.

Thanks,
jimbo99999

Here is my query:

Select
      A1.[Pro Number]
From
      [CUS0101].dbo.[Processing Table] A1 with (nolock)
Where
      A1.[Process Week] = '8/21/2015'
      And Exists (Select 1 From [BOR0102].dbo.[Processing Table] A2 with (nolock)
              Where (A2.[Pro Number] = A1.[Pro Number])
       And A2.[Pro Code] = A1.[Pro Code]
              And A2.[Pro Occurrence] = A1.[Pro Occurrence]
              And A2.[BOL Number] = A1.[BOL Number]
              And A2.[Sales Order Number] = A1.[Sales Order Number])

Lee

I assume [Pro Number] is not an integer field? You could just cast it.

Where (cast(cast(a2.[pro number] as int) as nvarchar(100) = cast(cast(a1.[pro number] as int) as nvarchar(100))

Open in new window

I am assuming that [pro number] is an nvarchar(100). Change that datatype to what you need. Would that work?

Jimbo99999

ASKER

Hello:

Thanks for responding...it is varchar(20).

Will that work for this [Pro Number] value --> 0000046F66162

I am not very good at all with casting so I appreciate the assistance.

Thanks,
jimbo99999

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Instead of attempting to strip leading zeros you try the reverse approach e.g.

where
right('00000000000000000000' + [this_column]),20)
=
right('00000000000000000000' + [that_column]),20)