Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please Provide Me with Best Solution


Please Provide Me with Best Solution

Author
Message
Scott1336
Scott1336
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 135
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
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, MVP, M.Sc (Comp Sci)
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


herladygeekedness
herladygeekedness
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 813
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
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, MVP, M.Sc (Comp Sci)
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


geoff.parsons
geoff.parsons
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 139
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.
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 6490
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? :-D

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search