using not like - 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

General SQL Server Forums

New to SQL Server Programming

using not like

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-11-05 : 15:32:26

Hi guys,

I am writing query to output all those customers who does not have a home number, cell number, work number beginning with 720,721,722,724,725,726,783,785,788.

I am using not like and it only outputs those customers who have information in all three fields below is a sample


268-460-1633	268-771-5198	268-728-9792
268-462-6940	268-772-6940	268-462-2922
268-462-8015	268-770-0908	268-462-0061
268-560-1953	268-770-1595	268-480-2636

So the code is not outputting people who may have the following combination but should still qualify to be on the list


NULL	2687238147	2684818860
NULL	2687284219	NULL
NULL	268-771-0497	268-462-5461
[code]

Do any one have any suggestions as to why this is happening??

Below is the code I am using

[code]
select c.cardnumber
	,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,HomePhone
	,CellPhone
	,WorkPhone
	
	,case
		when linked = 'Y' then 'Y'
			else 'N'
			end LINKED  into digilinkedantigua
from customer c 
	left outer JOIN 
	(
		SELECT 	distinct CARDNUMBER
	 		,case when count(*) > 0 Then 'Y'
				else 'N'
				END LINKED
			
		FROM 	AlternateAccessKeys
	
		WHERE
       			(
				  (AlternateCardNumber LIKE '268720%')
				or (AlternateCardNumber LIKE '268721%')
				or (AlternateCardNumber LIKE '268722%')
				or (AlternateCardNumber LIKE '268724%')
				or (AlternateCardNumber LIKE '268725%')
				or (AlternateCardNumber LIKE '268726%')
				or (AlternateCardNumber LIKE '268783%')
				or (AlternateCardNumber LIKE '268785%')
				or (AlternateCardNumber LIKE '268788%')
				
				 or (AlternateCardNumber LIKE '268 720%')
				or (AlternateCardNumber LIKE '268 721%')
				or (AlternateCardNumber LIKE '268 722%')
				or (AlternateCardNumber LIKE '268 724%')
				or (AlternateCardNumber LIKE '268 725%')
				or (AlternateCardNumber LIKE '268 726%')
				or (AlternateCardNumber LIKE '268 783%')
				or (AlternateCardNumber LIKE '268 785%')
				or (AlternateCardNumber LIKE '268 788%')
				
			)
			AND len(AlternateCardNumber) = 10	
			GROUP BY CARDNUMBER
	

	)data6 on data6.cardnumber=c.cardnumber
where c.cardnumber like '25252545%'

	
	and CellPhone not LIKE '268720%'
	and CellPhone not LIKE '268721%'
	and CellPhone not LIKE '268722%'
	and CellPhone not LIKE '268724%'
	and CellPhone not LIKE '268725%'
	and CellPhone not LIKE '268726%'
	and CellPhone not LIKE '268783%'
	and CellPhone not LIKE '268785%'
	and CellPhone not LIKE '268788%'
	
	
	and  CellPhone not LIKE '268-720%'
	and CellPhone not LIKE '268-721%'
	and CellPhone not LIKE '268-722%'
	and CellPhone not LIKE '268-724%'
	and CellPhone not LIKE '268-725%'
	and CellPhone not LIKE '268-726%'
	and CellPhone not LIKE '268-783%'
	and CellPhone not LIKE '268-785%'
	and CellPhone not LIKE '268-788%'
	

	and  CellPhone not LIKE '720%'
	and CellPhone not LIKE '721%'
	and CellPhone not LIKE '722%'
	and CellPhone not LIKE '724%'
	and CellPhone not LIKE '725%'
	and CellPhone not LIKE '726%'
	and CellPhone not LIKE '783%'
	and CellPhone not LIKE '785%'
	and CellPhone not LIKE '788%'
	

	and  CellPhone not LIKE '268 720%'
	and CellPhone not LIKE '268 721%'
	and CellPhone not LIKE '268 722%'
	and CellPhone not LIKE '268 724%'
	and CellPhone not LIKE '268 725%'
	and CellPhone not LIKE '268 726%'
	and CellPhone not LIKE '268 783%'
	and CellPhone not LIKE '268 785%'
	and CellPhone not LIKE '268 788%'
	

	and  WorkPhone not LIKE '268720%'
	and WorkPhone not LIKE '268721%'
	and WorkPhone not LIKE '268722%'
	and WorkPhone not LIKE '268724%'
	and WorkPhone not LIKE '268725%'
	and WorkPhone not LIKE '268726%'
	and WorkPhone not LIKE '268783%'
	and WorkPhone not LIKE '268785%'
	and WorkPhone not LIKE '268788%'
	
	
	and  WorkPhone not LIKE '268-720%'
	and WorkPhone not LIKE '268-721%'
	and WorkPhone not LIKE '268-722%'
	and WorkPhone not LIKE '268-724%'
	and WorkPhone not LIKE '268-725%'
	and WorkPhone not LIKE '268-726%'
	and WorkPhone not LIKE '268-783%'
	and WorkPhone not LIKE '268-785%'
	and WorkPhone not LIKE '268-788%'
	

        and  WorkPhone not LIKE '720%'
	and WorkPhone not LIKE '721%'
	and WorkPhone not LIKE '722%'
	and WorkPhone not LIKE '724%'
	and WorkPhone not LIKE '725%'
	and WorkPhone not LIKE '726%'
	and WorkPhone not LIKE '783%'
	and WorkPhone not LIKE '785%'
	and WorkPhone not LIKE '788%'
	
	
	and  WorkPhone not LIKE '268 720%'
	and WorkPhone not LIKE '268 721%'
	and WorkPhone not LIKE '268 722%'
	and WorkPhone not LIKE '268 724%'
	and WorkPhone not LIKE '268 725%'
	and WorkPhone not LIKE '268 726%'
	and WorkPhone not LIKE '268 783%'
	and WorkPhone not LIKE '268 785%'
	and WorkPhone not LIKE '268 788%'
	
	and HomePhone not LIKE '268720%'
	and HomePhone not LIKE '268721%'
	and HomePhone not LIKE '268722%'
	and HomePhone not LIKE '268724%'
	and HomePhone not LIKE '268725%'
	and HomePhone not LIKE '268726%'
	and HomePhone not LIKE '268783%'
	and HomePhone not LIKE '268785%'
	and HomePhone not LIKE '268788%'
	
	and HomePhone not LIKE '268-720%'
	and HomePhone not LIKE '268-721%'
	and HomePhone not LIKE '268-722%'
	and HomePhone not LIKE '268-724%'
	and HomePhone not LIKE '268-725%'
	and HomePhone not LIKE '268-726%'
	and HomePhone not LIKE '268-783%'
	and HomePhone not LIKE '268-785%'
	and HomePhone not LIKE '268-788%'
	

	and HomePhone not LIKE '268 720%'
	and HomePhone not LIKE '268 721%'
	and HomePhone not LIKE '268 722%'
	and HomePhone not LIKE '268 724%'
	and HomePhone not LIKE '268 725%'
	and HomePhone not LIKE '268 726%'
	and HomePhone not LIKE '268 783%'
	and HomePhone not LIKE '268 785%'
	and HomePhone not LIKE '268 788%'
	

	and HomePhone not LIKE '720%'
	and HomePhone not LIKE '721%'
	and HomePhone not LIKE '722%'
	and HomePhone not LIKE '724%'
	and HomePhone not LIKE '725%'
	and HomePhone not LIKE '726%'
	and HomePhone not LIKE '783%'
	and HomePhone not LIKE '785%'
	and HomePhone not LIKE '788%'
	
	
	and AddressLine1 is not null
	and lastname is not null
	
	 
GROUP BY C.CARDNUMBER,LINKED,FirstName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,HomePhone
	,CellPhone
	,WorkPhone
	
ORDER BY LINKED

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-05 : 15:44:26

try the NOT IN clause in your WHERE clause, instead of all those LIKES
where c.cardnumber like '25252545%'
and left(cellphone,3) not in('720','721','722','724','725','726','783','785','788)
and left(workphone,3) not in('720','721','722','724','725','726','783','785','788)
and left(homephone,3) not in('720','721','722','724','725','726','783','785','788)

JIM

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-11-05 : 21:40:42

ok i will try this thanks alot jimf

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources