how to compare rows from oldtbl to curtbl using Like?

oldtbl contains these CoNames
'abc'
'def'
'ghi'
'jkl'
curtbl contains these CoNames
'abc inc'
'def inc'
'jkl inc'
Note that curtbl does not contain 'ghi inc'
I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
select Coname from oldtbl where CoName Like (select CoName from curTbl)
select t1.CoName from oldtbl t1 join curtbl t2 On
t1.CoName Like t2.CoName --this did not return anthing
Is there a way to do this using Like? If yes, what is the correct syntax?
If not, is there another way to compare these rows?
Thanks,
RichI had my tables backwards. I need to retrieve rows from curtbl where CoName
is like oldtbl.CoName. I tried this syntax which actually did return rows,
but is quite slow.
select t1.CoName from curtbl t1 join oldtbl t2 on
t1.CoName like '%'+t2.CoName+'%'
My other alternative is to loop through oldtbl (all distinct CoNames) using
a cursor. I am just checking if there is a better way than a cursor.
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||then try this
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
"Rich" wrote:
> I had my tables backwards. I need to retrieve rows from curtbl where CoNa
me
> is like oldtbl.CoName. I tried this syntax which actually did return rows
,
> but is quite slow.
> select t1.CoName from curtbl t1 join oldtbl t2 on
> t1.CoName like '%'+t2.CoName+'%'
> My other alternative is to loop through oldtbl (all distinct CoNames) usin
g
> a cursor. I am just checking if there is a better way than a cursor.
>
>
> "Rich" wrote:
>|||Hi Omnibuzz, its me again :)
Thank you for your reply. I tried your method. It works, but wasn't any
faster than the one that I tried. But I believe your methos is probably mor
e
accurate. I did try a cursor, but I am still having a problem setting up
delimeters. I am still not clear on the rule for delimiting things in
Dynamic Sql. The one thing about the cursor is that I can print out a count
to let me know the progress of the cursor. Here is the cursor - which has a
syntax error in the sql statement. Any suggestions appreciated how to fix
this. Note: I am using the actual col names and table names here.
Declare @.coid varchar(50), @.i int
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp3
set @.i = 1
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.i
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
set @.i = @.i + 1
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
Thanks,
Rich
"Omnibuzz" wrote:
> use this.. and let me know if it works
> select a.CoName from oldTbl a
> where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName
+
> '%')
>
> --
>
>
> "Rich" wrote:
>|||Hi Rich..
I would say its better to use a query than this approach. try to index on
that column and see.
your approach is a double killer. a cursor and dynamic sql.. whooooh.. lets
keep the cursor out for this problem.
Hope this helps.
--
"Rich" wrote:
> Hi Omnibuzz, its me again :)
> Thank you for your reply. I tried your method. It works, but wasn't any
> faster than the one that I tried. But I believe your methos is probably m
ore
> accurate. I did try a cursor, but I am still having a problem setting up
> delimeters. I am still not clear on the rule for delimiting things in
> Dynamic Sql. The one thing about the cursor is that I can print out a cou
nt
> to let me know the progress of the cursor. Here is the cursor - which has
a
> syntax error in the sql statement. Any suggestions appreciated how to fix
> this. Note: I am using the actual col names and table names here.
>
> Declare @.coid varchar(50), @.i int
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp3
> set @.i = 1
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.i
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> set @.i = @.i + 1
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> Thanks,
> Rich
> "Omnibuzz" wrote:
>|||Well I am currently running
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
as we speak (as I type). On the cursor, I can limit my old table to like 10
CoNames. So I iterate only 10 times in the cursor. My thing is that I was
trying out the cursor just for exercise - mostly on writing dynamic sql. Ma
y
I request if you could correct my syntax on the sql statement?
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
I fetch the CoName into @.coid. I am trying to simulate
Insert Into #temp4 select recordID, coid from subscriber where coid like
'%abc%'
How do I delimit @.coid above ?
Thanks again for your help
Rich
"Omnibuzz" wrote:
> Hi Rich..
> I would say its better to use a query than this approach. try to index
on
> that column and see.
> your approach is a double killer. a cursor and dynamic sql.. whooooh.. let
s
> keep the cursor out for this problem.
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||use this
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like ''%''' + @.coid + '''%''')
Hope this helps.
--
"Rich" wrote:
> Well I am currently running
> select t1.CoName from curtbl t1 join oldtbl t2 on
> charindex('%'+t2.CoName+'%',t1.CoName) >0
> as we speak (as I type). On the cursor, I can limit my old table to like
10
> CoNames. So I iterate only 10 times in the cursor. My thing is that I wa
s
> trying out the cursor just for exercise - mostly on writing dynamic sql.
May
> I request if you could correct my syntax on the sql statement?
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> I fetch the CoName into @.coid. I am trying to simulate
> Insert Into #temp4 select recordID, coid from subscriber where coid like
> '%abc%'
> How do I delimit @.coid above ?
> Thanks again for your help
> Rich
>
> "Omnibuzz" wrote:
>|||Thank you. Well, the cursor ran this time, but I got an error message for
each of 10 iterations in the cursor. Note: I modified the cursor to only
select (and not insert - for if there is no match don't insert a null row.
Here is my modified cursor and the error message - using only the select
statment. Could it be that since none of the 10 CoNames I am passing in hav
e
a match in the current table ?
----
Declare @.coid varchar(50)
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp5
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
----
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'NEW'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TRENWICK'.
Server: Msg 170, Level 15, State 1, Line 1
...
Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
against:
'NEW CENTURY ENERGIES INC-CO'
'TRENWICK AMERICA CORP'
...
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual tbl
"Omnibuzz" wrote:
> use this
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like ''%''' + @.coid + '''%''')
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||Well, I modified the delimiters as follows which ended up working in the cur
sor
exec('select recordID, CoID from subscriber where CoID Like ''%' + @.coid +
'%''')
And when I used a smaller number of CoNames, your method
select a.coid from subscriber a
where exists ( select 1 from #temp6 b where b.coid like '%' + a.coid + '%')
worked very well. I really appreciate all of your help. Thank you very
much.
Rich
"Rich" wrote:
> Thank you. Well, the cursor ran this time, but I got an error message for
> each of 10 iterations in the cursor. Note: I modified the cursor to only
> select (and not insert - for if there is no match don't insert a null row.
> Here is my modified cursor and the error message - using only the select
> statment. Could it be that since none of the 10 CoNames I am passing in h
ave
> a match in the current table ?
> ----
--
> Declare @.coid varchar(50)
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp5
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> ----
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'NEW'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'TRENWICK'.
> Server: Msg 170, Level 15, State 1, Line 1
> ...
> Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
> against:
>
> 'NEW CENTURY ENERGIES INC-CO'
> 'TRENWICK AMERICA CORP'
> ...
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual t
bl
>
>
> "Omnibuzz" wrote:
>