Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2012-09-19 : 07:29:24
|
| I have a table that has a columns ID and EMAIL_ADDRESSI am trying to list only columns that do not have .com, .net .edu etcin the example below just list id 119 ID EMAIL_ADDRESS117 aaaaaaa@COM118 BBBBBBB@CCCCCC.com119 123-454-2222 120 eeeee@ffff.net 123 gggggg@sbcglobal.netso I wrote the following SELECT EDI_SOURCE_PRIMARY_KEY_VALUE, EMAIL_ADDRESS FROM TDC_EXPORT_ADDRESS WHERE EMAIL_ADDRESS NOT LIKE ('.COM%')OR EMAIL_ADDRESS NOT LIKE ('.NET%') OR EMAIL_ADDRESS NOT LIKE ('.EDU%')OR EMAIL_ADDRESS NOT LIKE ('.ORG%')but it is not doing what I expect from NOT LIKE I am sure I am doing something wrong... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-19 : 07:34:14
|
not or not on the same column is always true.Replace OR by AND
Too old to Rock'n'Roll too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-19 : 07:35:42
|
and change '.COM%' to '%.COM' and so on...
Too old to Rock'n'Roll too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-19 : 07:36:55
|
and 117 will also be listed because there is no dot
Too old to Rock'n'Roll too young to die. |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-09-19 : 07:51:43
|
| Webfred thanks for that answer... from my question I am sure you have deduced that I am trying to clean up a demographic table and here is another question how do I go about list ID where the phone number is not in the following format (999) 999-9999 I am trying to find fields that are missing digits in the phone numbers like (234) 222-999 |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-19 : 07:59:14
|
First I would like to say that you should not store formatted data in the database table. Formatting should be done in the front end.select * from tablewhere phonenumber not like '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
Too old to Rock'n'Roll too young to die. |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-09-19 : 08:23:20
|
| Thank you very much and your point is very well taken but unfortunately I am cleaning up someone else's is data...Have a great day you sure have made my day.... |
|
|
|
|
|
|
NOT LIKE statement