Dealinf with many many wildcards...

[woody2002]

Hopefully someone here can help me...

I have to generate a list of contacts from our database that has in excess of over 250,000 records. I get given a list of criteria by sales, for example they want to mail everyone in a particular Organisation and I then have to pull these from the db. The list provided will contain specific organisation names. However there are sometimes variations on those organisation names that still need to be pulled. E.g Barclays Bank is the name provided by sales, but there are entries in the db of "Barclays" or "Barclay Bank". With this in mind I have to do a wildcard search to pull the records:

select * from db where Org_Name like '%barclays%'

This is all well and good for 1 organisation name, but when I get given a list with over 10,000 it would take me a while to write all of these down. To over come this I run a formula in Excel to change the list of companies:

HSBC
Barclays
Capital Bank etc etc..

into ="or org_name like '%"&REF&"%", which produces

or Org_Name like '%HSBC%'
or Org_Name like '%Barclays%' etc

I then copy this into my query and run it as usual.

I've thought about creating a temporary table with the Organisation information and then just running the queries against the two tables, eg

Select ActualTable.Org_Name from ActualTable, TempTable
where ActualTable.Org_Name like '%' + tempTable.OrgName + '%'

but IT refuse to give a lowly Marketing Data Analyst rights to be able to do that.... Grrrrr

So my question is....is using excel to add the "or Org_Name like '%Org_Name%'" and then copying this to Query Analyzer the best way to do it, which of course mean my query is massive:

select * from table where (org_name like'%GCHQ%'
or org_name like'%Dresdner Kleinwort Wasserstein%'
or org_name like'%HBOS%'
or org_name like'%Abbey National%'
or org_name like'%ABN AMRO Bank%'
or org_name like'%Ashurst Morris Crisp%'
or org_name like'%Bytes Technology Group%'
or org_name like'%CO-OP%'
or org_name like'%EPSRC%'
or org_name like'%Equitas%'
or org_name like'%Exxon Mobil%'
or org_name like'%GCHQ%'
or org_name like'%Goldman Sachs%'
or org_name like'%Home Office%'
or org_name like'%Invalid Group%'
or org_name like'%NFU Mutual Insurance Group%'
or org_name like'%PriceWaterhouseCoopers%') etc etc

or is there another way?

Thanks for your input

W

 

[donutman]

You need a different approach to your solution to the project. How are you being given the organization names, on paper, disk file, verbally?
-Karl

 

[SQLBill]

Personally, I would take the time to clean up the variations and then have validation to make sure only the proper names get inputted.

How does the organization get inputted? Can you use drop down boxes to limit what names can be used?

-SQLBill

 

[woody2002]

Thanks for the replies guys...

Karl, the organisation names are given to me in an Excel spreadsheet. This is why I use Excel to generate the query "or org_name like..." blah blah

Bill, I do understand what you are saying and this is something I am slowly addressing, however I will add (and apologies as I didn't make this very clear) but the list of organisation names I am given tends to only include the parent company. I am then asked to pull all subsiduary companies associated with it. So I am given Barclays Bank, I then have to pull, Barclays Capital, Barclays Insurance etc etc...in addition to all instances where the org name has been entered incorrectly.

So any further suggestions?

Cheers

W

 

[nigelrivett]

You're assuming that Barclays Capital has something to do with Barclays.

That may be the case but it's a very dangerous assumption.
I would create a table with all the organisations and their parents and join to that to get the value.
The problem then becomes generating that table.

Presumably easy to get the list of orgs.
Then go through with a series of updates - the first time will be onerous but is worth a bit of care.
Just keep going untill you get rid of all the nulls.

Next time add any new org's and set them.
Now you can set manually any organisations that look the same but really aren't.
I'm thinking of things like MBNA where there is another company called MBNA equity ... but that is completely separate and being investigated to see whether they are allowed to use the name.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.