matching numbers between two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

matching numbers between two tables

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-11 : 18:03:29

I have two tables EFT 1 and EFT2
EFT1
----
EFT1938364
33457489
293723628
EFT182720
EFT6728292
338272611
293837333

EFT
----
EFT1938364
0033457489
000293723628
EFT182720
EFT6728292
338272611
293837333

I am trying to find the matching records basedon the matching logic

I can match up the number with EFT ( exact match)

However ,
Note that start with 33 may have 00 in front of them or may start with 33.
Note that start with 29 may have 000 in front of them or may start with 29.

How can I write query that use above matching logic between EFT1 abd EFT 2 table?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-11 : 21:53:33

Select *
from EFT a inner join EFT1 b
on a.COL1 = b.COL1

substitute the "COL1" with your column names.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-11 : 23:00:05

SELECT EFT1.*, EFT2.*
FROM EFT1 JOIN EFT2
ON EFT1.YourColumn = CONVERT(int, EFT2.YourColumn)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-14 : 10:30:44

I think you need something like this

-- prepare test data
declare @EFT1 table (num varchar(100))

insert	@eft1
select	'EFT1938364' union all
select	'33457489' union all
select	'293723628' union all
select	'EFT182720' union all
select	'EFT6728292' union all
select	'338272611' union all
select	'293837333'

declare @EFT2 table (num varchar(100))

insert	@eft2
select	'EFT1938364' union all
select	'0033457489' union all
select	'000293723628' union all
select	'EFT182720' union all
select	'EFT6728292' union all
select	'338272611' union all
select	'293837333'

-- do the magic
SELECT		eft1.*,
		eft2.*
FROM		@eft1 eft1
INNER JOIN	@eft2 eft2 ON SUBSTRING(eft2.num, PATINDEX('%[^0]%', eft2.num), 100) = SUBSTRING(eft1.num, PATINDEX('%[^0]%', eft1.num), 100)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources