Find the values of filed from one table searching through records in a second table

Hi Experts,

I have 2 tables.
Tb1 has ID1 and ID2 field , char (6) .
Tb2 has ID1 and Comment field.
I need to search through records of Tb2..comment  varchar (4000) and find values of ID2 and replace by ID1.


ID1 is numeric and ID2 is alphanumeric.

Example data:
tb1
---------
ID1     ID2
=============
999999  Oe123b
888888  e9874b

tb2
=================
ID1        Comment
234560  the entry is very similar to Oe123b. --->replace Oe123b with 999999
111111  See e9874b.                                        --->replace e9874b  with 888888


This code can’t even identify the entries that contain ID2.


DECLARE @ID2 CHAR(6)
--Set values for search  
set @ID2 = 'SELECT ID2 from tb1 where ID2 is not null'


SELECT DISTINCT a.ID1,a.Comment
FROM tb2  as a,
tb1 as b
WHERE a.Comment LIKE '% '+ @ID2 +'%'
AND b.ID2 is not null

Is there some way of using the replace function to update all instances.
Thank you.
PDFAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
Thanks Mark,

Indeed that was a typo, actually I forgot to remove the comma after I modified the original query.  

As for your concern for the multiple tb2 ID2 occurrences it is only valid if there would be a rule saying that only the first or last occurrence of a matched value of ID1 would be preferred. In the case of UPDATE solution I gave, will still work but with one of the ID1 values that will be matched to a multiple ID2 value(could be the last one after joining).

The UPDATE, now corrected, will work perfectly:
UPDATE a SET
	Comment=REPLACE(Comment, b.ID2, b.ID1)
FROM 
	tb2  as a
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

And to check before update you use:
SELECT
	a.ID1 as tb2ID,
	a.Comment as CommentBefore,
	REPLACE(Comment,b.ID2,b.ID1) as CommentAfter,
	b.ID1,
	b.ID2
FROM 
	tb2  as a
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

0
 
ZberteocCommented:
Try this:
UPDATE a SET
	Comment=REPLACE(Comment,b.ID2,b.ID1)
FROM 
	tb2  as a,
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

To check first you can run:
select
	a.ID1 as tb2ID,
	a.Comment as CommentBefore
	REPLACE(Comment,b.ID2,b.ID1) as CommentAfter,
	b.ID1,
	b.ID2
FROM 
	tb2  as a,
	inner join tb1 as b
		ON	a.Comment LIKE '% '+ b.ID2 +'%' 
		AND b.ID2 is not null

Open in new window

0
 
PDFAuthor Commented:
@ Zberteoc,

Thanks for the comment,
But , as I said my  query doesn’t do the job,
First I need to identify records where is update needed.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
OK, a couple of things to be careful with like char(6) with trailing spaces, or longer strings in the comment with a prefix matching the ID2 (e.g. '123456' will match with '12345678')

Having said that, the following works with the test scenario above...

-- first create some test tables

create table #TB1 (ID1 char(6), ID2 char(6))
create table #TB2 (ID1 char(6), Comment varchar(4000))

-- now insert our test data

insert #TB1 values ('999999', 'Oe123b')
insert #TB1 values ('888888', 'e9874b')

insert #TB2 values ('234560', 'the entry is very similar to Oe123b.') --->replace Oe123b with 999999
insert #TB2 values ('111111', 'See e9874b.')                          --->replace e9874b  with 888888
 
-- then run a test query

 select T2.*, T1.* 
 from #TB2 T2
 cross apply (select * from #TB1 T1 where charindex(T1.ID2, T2.Comment) > 0) T1

-- now do the update

 update T2 set comment = replace(comment,T1.id2,T1.id1)
 from #TB2 T2
 cross apply (select * from #TB1 T1 where charindex(T1.ID2, T2.Comment) > 0) T1

-- and finally, check the results

select * from #TB2

Open in new window

0
 
PDFAuthor Commented:
Works beautifully on a test data.
Let me do on real tables.
0
 
ZberteocCommented:
@PDF

I don't think you understood my solution. The update query will do the job in 1 step without any loops. What is the problem with that?
0
 
Mark WillsTopic AdvisorCommented:
Can confirm that Zberteoc's solution also works.

Small syntax error on the JOIN (remove the comma prior to the join, after the table alias 'a')
And a missing comma after the 'commentbefore' in the select.

Having just tested and fixed those small typo's, then I can confirm it does work as Zberteoc intended.

As for a loop, you might need one if there are multiple / different instances of ID2 within the comment.

For my code, as with Zberteoc's, you could encapsulate within a "while" loop using the select to test for a result / record count > 0

If that is the case, or a possibility, then the "while" loop would be worth considering.
0
 
PDFAuthor Commented:
Thank you @ Zberteoc & Mark !!!!

Now I have a dilemma how to fairly split points.
Any ideas or suggestions???
0
 
Mark WillsTopic AdvisorCommented:
I would be happy with sharing half and half...

But entirely up to you - it is your question :)
0
 
PDFAuthor Commented:
Thank you @ Zberteoc & Mark !!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.