| Author |
Topic |
|
ntn104
Posting Yak Master
175 Posts |
 Posted - 2011-08-04 : 10:32:22
|
| How do I exclude name that related to 'Bank' or 'Financial...' ect...but not take out those individual name that had last name or first name like 'BANK' or 'BANKS'I used the following, but it took those invidial out...such as:- RICHARD BANKS II - BANKS, JENIFFER- JOHN V BANKS....ECT...where (name not like '%BANK%' OR NAME NOT LIKE '%FINANCIAL%')Thanks, |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-04 : 10:43:38
|
| Are humans and Banks intermixed in the same column in the same table? If so, unless there is another column that identifies whether the name is that of a bank or of a human, I don't know of a way to exclude it. If you had a row which had "SG Cowen", there has to be some way to tell whether you are referring to a Sarah Cowen (a human being) or SG Cowen Securities (a financial services company) based on some other column. |
 |
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2011-08-04 : 10:51:26
|
They are in the same column...quote:
Originally posted by sunitabeck
Are humans and Banks intermixed in the same column in the same table? If so, unless there is another column that identifies whether the name is that of a bank or of a human, I don't know of a way to exclude it. If you had a row which had "SG Cowen", there has to be some way to tell whether you are referring to a Sarah Cowen (a human being) or SG Cowen Securities (a financial services company) based on some other column.
|
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-04 : 14:02:58
|
quote:
Originally posted by ntn104
They are in the same column...
Do you have any other column in your table that would help you identify a given row/name as belonging to a human being (for example, Gender) or as belonging to an institution (for example, number of employees or market cap)?If there is no such information, you would not be able to separate them out.I realize that you may have been given this database by your employer to make sense of and work on, so you may not have any choice. But if you do have a choice, redesign the database so humans won't occupy the same column (or for that matter the same table) as institutions. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-04 : 14:13:07
|
| [code]Declare @t table (name varchar(32))insert @t values ('First Bank')insert @t values ('Tony Banks')insert @t values ('Bank of the West')insert @t values ('Chase Bank and Trust')SELECT * from @twhere name not like '%BANK'And name not like 'Bank %'and name not like '% BANK %'[/code] |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-04 : 14:23:12
|
quote:
Originally posted by russell
Declare @t table (name varchar(32))insert @t values ('First Bank')insert @t values ('Tony Banks')insert @t values ('Bank of the West')insert @t values ('Chase Bank and Trust')SELECT * from @twhere name not like '%BANK'And name not like 'Bank %'and name not like '% BANK %'
I don't know russell.. I have the fear that many human beings may sneak in trying to pass themselves as banks. I looked up face book and there are a few gentlemen named "John Bank" there.  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-04 : 15:16:04
|
Good point. I wasn't thinking of Bank as a last name. Guess the OP is stuck manually reviewing 'em  |
|
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2011-08-06 : 01:04:39
|
I guess I have to find a list of standard bank names from that column to exclude them from the query. Thanks,quote:
Originally posted by sunitabeck
quote:
Originally posted by ntn104
They are in the same column...
Do you have any other column in your table that would help you identify a given row/name as belonging to a human being (for example, Gender) or as belonging to an institution (for example, number of employees or market cap)?If there is no such information, you would not be able to separate them out.I realize that you may have been given this database by your employer to make sense of and work on, so you may not have any choice. But if you do have a choice, redesign the database so humans won't occupy the same column (or for that matter the same table) as institutions.
|
|
|
|
|
SQL NOT LIKE question