Please Provide Me with Best Solution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Please Provide Me with Best Solution

Please Provide Me with Best Solution

Rate Topic

Display Mode

Topic Options

Author

Message

Scott1336

Posted Tuesday, July 31, 2012 4:05 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 1:09 PM
Points: 13, Visits: 79

This is an issue we had to solve for a customer a few weeks back. Imagine you had list
of 10,000 US business names stored in a free form text field in a database. There are no
descriptive attributes with the US business names indicating the type of business. A sample
set of the US business names would appear as follows:

CITGROUP

WALMART

IBM

SECU

NATIONS BANK

NBA

We have a need in our application to distinguish banks from non-banks. Thus, the issue was
that we had to determine which US business names belonged to US banks. Please describe how
would you go about approaching/solving this problem?

Note: All you have is the US business name. There is NO CHANCE of having an additional
attribute of some sort that indicates that it is a bank within the database where this information
is stored. Also we are not looking for an exact 100% solution nor are we looking for written
code, we just want to understand your thought process as to how you would go about solving
this problem.

Post #1338230

SQLRNNR

Posted Tuesday, July 31, 2012 4:28 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Well, since you can't alter the database, I don't think there is a good way to solve this issue.

Personally, I would opt for an attribute or for an additional table that lists all of the banks. Then compare the text field (in the case of the separate table listing the banks) to the banks table.

Other methods might involve a call out to fdic.gov on some sort of interface. That would also require a call out to NCUA for credit unions, imho. I don't think these are viable options in most cases because of the delay that could be caused.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1338240

GilaMonster

Posted Tuesday, July 31, 2012 4:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Obtain from somewhere a list of all banks (another database, government info, etc), then use SSIS fuzzy lookup.

Without that list of all banks, that's impossible to solve automatically. How would you tell if "Mutual & Federal" is a bank? Or "ABSA"?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1338244

herladygeekedness

Posted Wednesday, August 01, 2012 2:11 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:46 AM
Points: 240, Visits: 653

I would explain to the customer that if I can't create a db field to store this info, that hard-coding is a terrible solution. Not knowing how/whether the list of businesses is modified, it's not going to be maintainable if "Cash Now" becomes a bank business on the list.

If there is no ability for pushback, then, fine, I would case out matches to 'bank', 'credit union', maybe 'savings institution' and be done with it, explaining to the customer what the limitations are.

Post #1338802

Lowell

Posted Wednesday, August 01, 2012 3:23 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638, Visits: 27,713

a quick google search of "FDIC insured banks" gives me a starting point like Gail suggested:

http://www2.fdic.gov/idasp/
7200+ institutions on the search page , all part of a downloadable csv file.
you'd have to get a similar list for credit unions and savings and loans, and you are back to Gail's fuzzy lookup for matching the names.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1338851

GilaMonster

Posted Wednesday, August 01, 2012 3:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

And if you can't put another attribute in this database, you put a table into another with a lookup table once you've done the fuzzy lookup.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1338860

geoff.parsons

Posted Thursday, August 02, 2012 2:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:12 AM
Points: 6, Visits: 64

I used to do this kind of thing all the time in a company I worked at a few years ago.

I would query a list of all the companies in the UK (we had lots of company data - but you can easily obtain it), that were listed as a bank.

Then simply match on name - we didn't have SSIS back then and had to use third-party applications but Fuzzy Lookup would do the job I'm sure.

Post #1339002

Jan Van der Eecken

Posted Thursday, August 02, 2012 3:18 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 2,265, Visits: 5,948

GilaMonster (7/31/2012)

How would you tell if "Mutual & Federal" is a bank?

He, Mutual and Federal isn't a bank. Doesn't everyone know that? BigGrin

--------------------------------------------------------------------------

The function of good software is to make the complex appear to be simple. (Grady Booch)

Post #1339540

« Prev Topic | Next Topic »

Permissions