Solved

LIKE Search Logic: SQL Server 2008

Posted on 2011-02-11
5
245 Views
Last Modified: 2012-08-13
Hi Experts,
Trying to create search logic, and my write / execute, look for errors, try again method is taking far too long with over 8000 records to play with! Something new always pops up that needs fixing!

So, with COMMUNICATIONS as COM and ORGANISATIONS as O, any thoughts on how best to refine the search below to find PLACES OF LEARNING?

Note:
Exclude all cubs and brownies (NEGATE).
Email domains of .ac.uk and .sch.uk is only used by education & schools in the United Kingdom – however may also include cubs and brownies.
Not all schools have a .ac.uk or .sch.uk domain name
Some towns and schools have the word BROWN or CUB (rare) in it.
WHERE 
 COM.number LIKE '%.ac.uk' 
 OR COM.number LIKE '%.sch.uk%'
 OR O.name LIKE '%scho%'
 OR O.name LIKE '%pre-scho%' 
 OR O.name LIKE '%prescho%'
 OR O.name LIKE '%infant%' 
 OR O.name LIKE '%nursery%' 
 OR O.name LIKE '%primary%' 
 OR O.name LIKE '%secondary%' 
 OR O.name LIKE '%college%'
 OR O.name LIKE '%hysgol%' --Welsh for "schooling"
 OR O.name LIKE '%ysgol%' --Welsh for school.
 OR O.name LIKE '%kinderg%'
 OR O.name LIKE '%kinter%'
 OR O.name LIKE '%brown[^ies]%' --negate brownies

Open in new window

Thanks in advance!

John Diddy
0
Comment
Question by:JohnDiddy77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34870633
post your full query...

#you haven't really expalined what you're trying to achieve and what its based upon....

however at least 3 of the conditions are unnecessary as you already covered them

a fulltext search may be more appropriate .... again more background information
on the data you are searching....


WHERE 
 COM.number LIKE '%.ac.uk' 
 OR COM.number LIKE '%.sch.uk%'
 OR O.name LIKE '%scho%'
-- OR O.name LIKE '%pre-scho%' -- included in scho
-- OR O.name LIKE '%prescho%'  -- included in scho
 OR O.name LIKE '%infant%' 
 OR O.name LIKE '%nursery%' 
 OR O.name LIKE '%primary%' 
 OR O.name LIKE '%secondary%' 
 OR O.name LIKE '%college%'
-- OR O.name LIKE '%hysgol%' --Welsh for "schooling" -- included in ysgol
 OR O.name LIKE '%ysgol%' --Welsh for school. 
 OR O.name LIKE '%kinderg%'
 OR O.name LIKE '%kinter%'
 OR O.name LIKE '%brown[^ies]%' --negate brownies

Open in new window

0
 

Author Comment

by:JohnDiddy77
ID: 34870722
SELECT DISTINCT
	isnull(C.forenames + ' ', '') + isnull(C.surname, '') AS 'Label Name',
	C.forenames AS 'Forename',
	C.surname AS 'Surname',
	O.name AS 'Organisation',	
	MAX(CASE COM.device WHEN 'EM' THEN number END) AS Email,
	MAX (CASE AC.campaign WHEN 'MPC' THEN 'Y' ELSE 'N' END) AS '2008/09',
	MAX (CASE AC.campaign WHEN 'P09' THEN 'Y' ELSE 'N' END) AS '2009/10',
	MAX (CASE AC.campaign WHEN 'P10' THEN 'Y' ELSE 'N' END) AS '2010/11'

FROM dbo.appeal_collections AS AC
	
	INNER JOIN unmanned_collections AS UMC
		ON AC.collection_number = UMC.collection_number
		
	INNER JOIN dbo.organisations AS O
		ON UMC.organisation_number = O.organisation_number

	INNER JOIN contacts AS C
		ON UMC.contact_number = C.contact_number
	
	INNER JOIN dbo.communications AS COM
		ON C.contact_number = COM.contact_number

WHERE
	(AC.campaign ='MPC' OR AC.campaign = 'P09' OR AC.campaign = 'P10')
	
	AND COM.device = 'EM' --also check for inverted comma.
	
	AND (COM.number LIKE '%.ac.uk' 
		OR COM.number LIKE '%.sch.uk%'
		OR O.name LIKE '%scho%'
		OR O.name LIKE '%pre-scho%' 
		OR O.name LIKE '%prescho%'
		OR O.name LIKE '%infant%' 
		OR O.name LIKE '%nursery%' 
		OR O.name LIKE '%primary%' 
		OR O.name LIKE '%secondary%' 
		OR O.name LIKE '%college%'
		OR O.name LIKE '%hysgol%'	
		OR O.name LIKE '%ysgol%’
OR O.name LIKE '%kinderg%'
		OR O.name LIKE '%kinter%'
		OR O.name LIKE '%brown[^ies]%'	
		)

Open in new window

SELECT DISTINCT
	isnull(C.forenames + ' ', '') + isnull(C.surname, '') AS 'Label Name',
	C.forenames AS 'Forename',
	C.surname AS 'Surname',
	O.name AS 'Organisation',	
	MAX(CASE COM.device WHEN 'EM' THEN number END) AS Email,
	MAX (CASE AC.campaign WHEN 'MPC' THEN 'Y' ELSE 'N' END) AS '2008/09',
	MAX (CASE AC.campaign WHEN 'P09' THEN 'Y' ELSE 'N' END) AS '2009/10',
	MAX (CASE AC.campaign WHEN 'P10' THEN 'Y' ELSE 'N' END) AS '2010/11'

FROM dbo.appeal_collections AS AC
	
	INNER JOIN unmanned_collections AS UMC
		ON AC.collection_number = UMC.collection_number
		
	INNER JOIN dbo.organisations AS O
		ON UMC.organisation_number = O.organisation_number

	INNER JOIN contacts AS C
		ON UMC.contact_number = C.contact_number
	
	INNER JOIN dbo.communications AS COM
		ON C.contact_number = COM.contact_number

WHERE
	(AC.campaign ='MPC' OR AC.campaign = 'P09' OR AC.campaign = 'P10')
	
	AND COM.device = 'EM'
	
	AND (COM.number LIKE '%.ac.uk' 
		OR COM.number LIKE '%.sch.uk%'
		OR O.name LIKE '%scho%'
		OR O.name LIKE '%pre-scho%' 
		OR O.name LIKE '%prescho%'
		OR O.name LIKE '%infant%' 
		OR O.name LIKE '%nursery%' 
		OR O.name LIKE '%primary%' 
		OR O.name LIKE '%secondary%' 
		OR O.name LIKE '%college%'
		OR O.name LIKE '%hysgol%'	
		OR O.name LIKE '%ysgol%’
OR O.name LIKE '%kinderg%'
		OR O.name LIKE '%kinter%'
		OR O.name LIKE '%brown[^ies]%'	
		)

Open in new window

0
 

Author Comment

by:JohnDiddy77
ID: 34870742
@ LowFat:

As alluded to in my original post, I'm searching organisation names and email addresses to find schools / and any form of educational establishment. However, I wish to exclude after-school clubs and similar activites with "Cubs" and "Brownies" being the main culprits.

I hope this is clearly.

PS Don't know why it's posting the code twice!
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34871273
ok ....

i think you need to analyse what words/phrases are in your data... use the first...

and then make a decision based on the frequency...

and add them into the exclusion list in the main query
select word,count(distinct o.organisation_number) as InOrgs,count(*) as Occurs
  from (
select substring(o.name+' ',v.number,charindex(' ',o.name+' ',v.number)-v.number) as word
     ,o.organisation_number
  from master.dbo.spt_,values as v
  cross join (select replace(replace(replace(replace(replace(name,'.',' '),',',' '),'(',' '),')',' '),';',' ')
                   as  name
                     ,organisation_number
                from dbo.organisations) as o
 where v.type='p'
 and v.number between 1 and len(o.name)+1
 and substring(' '+o.name,v.number,1)=' '
 ) as x
group by word
order by 3 desc,2 desc,1
 



SELECT 
	isnull(forename + ' ', '') + isnull(surname, '') AS 'Label Name'
        ,x.*
  from (
Select 	C.forenames AS 'Forename',
	C.surname AS 'Surname',
	O.name AS 'Organisation',	
	MAX(com.number ) AS Email,
	MAX (CASE AC.campaign WHEN 'MPC' THEN 'Y' ELSE 'N' END) AS '2008/09',
	MAX (CASE AC.campaign WHEN 'P09' THEN 'Y' ELSE 'N' END) AS '2009/10',
	MAX (CASE AC.campaign WHEN 'P10' THEN 'Y' ELSE 'N' END) AS '2010/11'

FROM dbo.appeal_collections AS AC
INNER JOIN unmanned_collections AS UMC
   oN AC.collection_number = UMC.collection_number
INNER JOIN dbo.organisations AS O
   ON UMC.organisation_number = O.organisation_number
INNER JOIN contacts AS C
   ON UMC.contact_number = C.contact_number
INNER JOIN dbo.communications AS COM
   ON C.contact_number = COM.contact_number

WHERE 	AC.campaign in ('MPC', 'P09', 'P10')
  AND COM.device = 'EM'
  AND (COM.number LIKE '%.ac.uk' 
		OR COM.number LIKE '%.sch.uk%'
		OR O.name LIKE '%scho%'
--		OR O.name LIKE '%pre-scho%' 
--		OR O.name LIKE '%prescho%'
		OR O.name LIKE '%infant%' 
		OR O.name LIKE '%nursery%' 
		OR O.name LIKE '%primary%' 
		OR O.name LIKE '%secondary%' 
		OR O.name LIKE '%college%'
--		OR O.name LIKE '%hysgol%'	
		OR O.name LIKE '%ysgol%’
                OR O.name LIKE '%kinderg%'
		OR O.name LIKE '%kinter%'
	)
group by forenames,surname,o.name
) as x
Where x.Organisation not like '%brownies%'
  and x.organisation not like '%club%'
  and x.organisation not like '% cub%'
  and x.organisation not like '%beaver%'
  and x.organisation not like '%rainbow%'
  and x.organisation not like '%scout%'
  and x.organisation not like '%Guide%'
  and x.organisation not like '%TWGA%'
order by x.organisation

Open in new window

0
 

Author Comment

by:JohnDiddy77
ID: 34927795
@ LowFat:
 master.dbo.spt_values?!

Really!?!

That, right there, is a monster! Your original code above didn't work so had to step through it and figure out the analysis bit, and learned a lot!

Question

Re: Analysis Section, how can I adapt this to work for a specific subset of of the Organisation Table, namely the organisations involved in my query above?
SELECT 
  WORD,
  COUNT(DISTINCT x.organisation_number) AS InOrgs,
  COUNT(*) AS Occurs

FROM 
(
SELECT 
		SUBSTRING(o.name + ' ',v.number,
		CHARINDEX(' ',o.name+ ' ',v.number)-v.number) AS word, o.organisation_number
		
	FROM
		master.dbo.spt_values AS v
             CROSS JOIN
		 (SELECT 
		   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,'.',' '),',',' '),'(',' '),')',' '),';',' ') AS  name, organisation_number
  FROM dbo.organisations) AS O
		
		  WHERE v.type='p'
		   AND v.number BETWEEN 1 AND len(o.name)+1
		  AND substring(' '+o.name,v.number,1)=' '
) AS x

GROUP BY word

ORDER BY 3 DESC,2 DESC,1

Open in new window

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question