loop through "like" records - 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 2012 Forums

Transact-SQL (2012)

loop through "like" records

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2014-06-06 : 13:53:01

Hi there,

I have a request to loop through 309 records to see which of them are "like" a service number field in another table. So an example would be use the beginning string abd123@ and tell me is there is an email address for that account in your table.

I know I have to do a cursor or a while loop but how does one account for the like part?

This is what I have so far:


DECLARE @ServiceID char(11) 
DECLARE c1 

CURSOR READ_ONLY 
FOR 
SELECT servicename 
FROM emailList

OPEN c1 

FETCH NEXT FROM c1 
INTO @ServiceID 

WHILE @@FETCH_STATUS = 0 
BEGIN 

	PRINT @ServiceID 
	
	select *
	from UsgSvc u
	where ServiceNumber like @ServiceID%
	
	FETCH NEXT FROM c1 
	INTO @ServiceID 

END 

CLOSE c1 
DEALLOCATE c1

Any help would be great

Thanks

Laura

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 14:14:04

quote:
Originally posted by lmayer4

Hi there,

I have a request to loop through 309 records to see which of them are "like" a service number field in another table. So an example would be use the beginning string abd123@ and tell me is there is an email address for that account in your table.

I know I have to do a cursor or a while loop but how does one account for the like part?

This is what I have so far:

DECLARE @ServiceID char(11)
DECLARE c1

CURSOR READ_ONLY
FOR
SELECT servicename
FROM emailList

OPEN c1

FETCH NEXT FROM c1
INTO @ServiceID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ServiceID

select *
from UsgSvc u
where ServiceNumber like @ServiceID%

FETCH NEXT FROM c1
INTO @ServiceID

END

CLOSE c1
DEALLOCATE c1

Any help would be great

Thanks

Laura

Rather than use a cursor, you could use a set-based query, for example like shown below. Set-based queries are almost always more efficient, often by one or two orders of magnitude.

SELECT *
FROM UsgSvc u
WHERE EXISTS
(
	SELECT *
	FROM emailList e
	WHERE u.ServiceNumber LIKE e.servicename + '%'
);

The like clause above looks for any ServiceNumber in the UsgSvc table that begins with a string that is in the ServiceName column of the emaillist table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-06 : 14:14:55

No cursor, no loop.

select u.*
from UsgSvc u
join emailList e
on e.servicename LIKE u.ServiceNumber + '%'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lmayer4
Starting Member

33 Posts

Posted - 2014-06-06 : 14:15:33

Thanks James! Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources