Find the values of filed from one table searching through records in a second table
Posted on 2014-03-23
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.
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.