Join question

# Join question

• יום חמישי 23 אוגוסט 2012 16:40

I need to do a join between 2 columns a.storenumber and b.storenumber

a.storenumber has leading zeros like 0100,0101,0103 for the same stores in b.storenumber its 100,101,103.. Will my join work ... or

How could i logic ally make it work ?to insert the leading zero if the column is 3 characters long and match off that

thank you

FM

### כל התגובות

• יום חמישי 23 אוגוסט 2012 16:44
מנחה דיון

Assuming the storenumber is 4 characters in the table A, try

from A JOIN B ON A.StoreNumber = RIGHT('0000' + LTRIM(b.StoreNumber),4)

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• יום חמישי 23 אוגוסט 2012 16:45

Just guess: No. Cause you are storing the StoreNumber in a [N]VARCHAR() column. In this case you can try to use CAST(), e.g. JOIN CAST(A.StoreNumber AS INT) = B.StoreNumber. Or TRY_CAST() when you're using SQL Server 2012.

• יום חמישי 23 אוגוסט 2012 16:45
מנחה דיון

I suggest converting the strings to numbers -- assuming the second are numbers.  For example:'

```declare @t1 table (a varchar(5) );
insert into @t1
select '0100' union all select '0101' union all
select '0103' union all select 'x101'
;
declare @t2 table (b int);
insert into @t2
select 100 union all select 101 union all
select 103
;
select
a,
b
from @t1 x
join @t2 y
on y.b = cast(left(a, len(a)*nullif(isnumeric(a),0)) as integer) ;
/* -------- Output: --------
a     b
----- -----------
0100  100
0101  101
0103  103
*/```

• יום חמישי 23 אוגוסט 2012 16:47

You can make it work.  Just zero pad the second column in your join statement:

```Select	*
From	Table1 t1
join	Table2 t2
on	t1.Col1 = Right('00000' + Convert(varchar(4), t2.Col1), 4)```

• סומן כתשובה על-ידי יום חמישי 23 אוגוסט 2012 19:48
•