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 |
|
nhaas
Yak Posting Veteran
90 Posts |
 Posted - 2007-10-18 : 12:03:09
|
| Good day All.I have been asked to revamp a statement that it working, and they want to exclude some additional information for ease of sorting out information by hand.right now the query looks like this:Select "certain fields 55 of them" From TABLE Where [1] like PHONESTRING OR [2] like PHONESTRING OR [3] like PHONESTRING ....(55 in all)This works fine just looking for a phone number in each column, NORTEL TNB.....Now I have been asked to not show any column that has "CFW 8" OR "ADL" in it as these are user defind areas and we dont want to see them.So how do I need to do this? Select * from TABLE WHERE (KEY0 = PHONESTRING and PHONESTRING NOT LIKE 'CFW 8'% and PHONESTRING NOT LIKE 'ADL')for each field? Not a problem but I want to get it right the first timeThanks for any help you can provide, I will most likely put this into a Procedure in the NEAR futur |
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-10-18 : 14:58:58
|
| I guess I really need to find out how to search in the column and disallow certain things:(KEY0 LIKE PHONESTRING and NOT LIKE 'CFW 8%PHONESTRING' and NOT LIKE 'ADL%PHONESTRING') OR (KEY1 LIKE PHONESTRING and NOT LIKE 'CFW 8%PHONESTRING' and NOT LIKE 'ADL%PHONESTRING') . . . .Would this be correct? |
 |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-18 : 15:07:34
|
| Sound like a problem with your designSo, you have 55 phone columns?You should have a detail table to store phone numbers |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-10-18 : 18:46:02
|
| Actually the phone number can appear in any of the 55 columns. This is the way that Nortel works I guess - it really depends on the phone type because of how many different types of Phones that are offerened and it also depends on which features that you have added onto the phones.I guess too that this is really a multifunction script for a few things.the import script was build from looking att an asci dump from the phone switches.... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-19 : 01:12:42
|
quote:
Originally posted by nhaas
Select "certain fields 55 of them" From TABLE Where [1] like PHONESTRING OR [2] like PHONESTRING OR [3] like PHONESTRING ....(55 in all)
This is shorterSELECT * FROM "certain fields 55 of them" From TABLE Wherephonestring in ([1], [2], [3], [4], [5], ..., [55])
E 12°55'05.25"N 56°04'39.16" |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-10-19 : 11:57:15
|
| Peso Thank you, I will try the statement.How Can I do LIKE statements because alot of the fields contain characters in front of the actual numbers, ALSO how do I filter out "CFW 8" and "ADL" both are user defind fields that I dont want to see. Currently I do a search in each column like this"[1] like %PHONESTRING " but I am getting the results from "CFW 8" and "ADL" which I want to not show up thanks.thank you |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-10-19 : 12:20:59
|
| Peso, It looks like I cannot do a LIKE with the IN statementthanks |
|
|
|
|
|
|
|
|
Revamp a Query Statement