String matching for 2 tables - SQL Server Forums

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)

String matching for 2 tables

Author

Topic

treeskin
Starting Member

22 Posts

Posted - 2006-05-03 : 20:31:59

I would need to get a left outer join for the comparison, but my intention is to use Table A as the master table that ending with A, and search in Table B for any Product ID that have the same prefix without the A. I tried on Substring for the Product ID but doesn't work well.

What are the ways to get the below results?

The result will be Table A = 1234A, and it should return value 1234 from Table B.

Table A
Product ID
-----------
1234A
5678A
9834A
34567A

Table B
Product ID
-----------
1234
34567
8736
9834

The end results will be showing:
Table A Table B
Product ID Product ID
---------- -----------
1234A 1234
5678A
9834A 9834
34567A 34567

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 20:50:20

[code]
declare @tableA table
(
ProductID varchar(20)
)

declare @tableB table
(
ProductID varchar(20)
)
insert into @tableA
select '1234A' union all
select '5678A' union all
select '9834A' union all
select '34567A'

insert into @tableB
select '1234' union all
select '34567' union all
select '8736' union all
select '9834'

select a.ProductID, isnull(b.ProductID, '')
from @tableA a left join @tableB b
on a.ProductID like b.ProductID + '%'
[/code]

treeskin
Starting Member

22 Posts

Posted - 2006-05-04 : 01:55:36

Hi KH, why there is a need of UNION ALL while insert the value to the table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 01:57:17

The section in green is for me to generate the data for testing of the query in blue

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 04:05:09

Read about Union in sql server help file

Madhivanan

Failing to plan is Planning to fail

treeskin
Starting Member

22 Posts

Posted - 2006-05-23 : 23:17:10

KH and Madhivanan, what I am asking is why we need a UNION ALL? As we could just insert the value into the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 23:22:21

Yes. You can just insert directly into the table as below without the UNION ALL.

insert into @tableA select '1234A'
insert into @tableA select '5678A'

Using UNION ALL saves me some typing or copy & paste of the 'insert into ...'

Is this what you wanted to know ?

treeskin
Starting Member

22 Posts

Posted - 2006-05-23 : 23:25:00

And is there other way to perform the same intention?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 23:36:33

quote:
Originally posted by treeskin

And is there other way to perform the same intention?

This is the simplest direct way of creating / inserting sample data into a table.

treeskin
Starting Member

22 Posts

Posted - 2006-05-24 : 01:47:33

ok... thanks KH.. it was working for simple join.

marat
Yak Posting Veteran

85 Posts

Posted - 2006-05-24 : 01:58:07

declare @tableA table
(
ProductID varchar(20)
)

declare @tableB table
(
ProductID varchar(20)
)
insert into @tableA
select '1234A' union all
select '5678A' union all
select '9834A' union all
select '34567A' union all
select '3456A'

insert into @tableB
select '1234' union all
select '34567' union all
select '8736' union all
select '9834' union all
select '345'

--this is wrong
select a.ProductID, isnull(b.ProductID, '')
from @tableA a left join @tableB b
on a.ProductID like b.ProductID + '%'
--this would be right
select a.ProductID, isnull(b.ProductID, '')
from @tableA a left join @tableB b
on replace(a.ProductID,'A','') = b.ProductID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources