September 18, 2012 at 5:59 am
Hello All,
Iam using the below query to exclude some unwanted files.
The query is not giving me the desired results.I got the results when i replaced the OR with AND..I just wanted to know why the query is not providing me the results if the OR condition is included in the query.
SELECT * FROM FILE_PROCESS_LOG
where FILENAME LIKE 'FON_smp1_D062012_%'
and ((CHARINDEX('_1_',FILENAME)=0)
or (CHARINDEX('_2_',FILENAME)=0))
Your help would be appreciated
September 18, 2012 at 6:09 am
Can you provide some sample data in regards to file names and expected results.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 18, 2012 at 6:10 am
Are you trying to exclude "_1_" and "_2_" in the same filename?
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#FILE_PROCESS_LOG') IS NOT NULL
DROP TABLE #FILE_PROCESS_LOG;
CREATE TABLE #FILE_PROCESS_LOG ([FILENAME] VARCHAR(25));
INSERT INTO #FILE_PROCESS_LOG
(FILENAME)
VALUES ('FON_smp1_D062012_1_'), --(CHARINDEX('_2_', FILENAME) = 0
('FON_smp1_D062012_2_'),--(CHARINDEX('_1_', FILENAME) = 0
('FON_smp1_D062012_1_2_'),--(CHARINDEX('_1_', FILENAME) != 0, (CHARINDEX('_2_', FILENAME) != 0
('FON_smp1_D062012_3_');--(CHARINDEX('_1_', FILENAME) = 0, (CHARINDEX('_2_', FILENAME) = 0
SELECT *
FROM #FILE_PROCESS_LOG
WHERE FILENAME LIKE 'FON_smp1_D062012_%'
AND ((CHARINDEX('_1_', FILENAME) = 0)
OR (CHARINDEX('_2_', FILENAME) = 0));
I'm assuming your data looks something like that. I put a comment after each line of the insert, indicating why the line would return or not return with the query as-written. The only one that doesn't fill the criteria, and is thus excluded, is the one where neither CharIndex() call returns 0. Change "OR" to "AND", and the only row that will qualify is the one with a 3 in it (the last one).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 18, 2012 at 6:27 am
Thank you all for the inputs.
When i execute this i get the below results
SELECT *
FROM #FILE_PROCESS_LOG
WHERE FILENAME LIKE 'FON_smp1_D062012_%'
AND ((CHARINDEX('_1_', FILENAME) = 0))
or (CHARINDEX('_2_', FILENAME) = 0);
FON_smp1_D062012_1_
FON_smp1_D062012_2_
FON_smp1_D062012_3_
I just want to know why the OR condition is not eliminating the _1_ and _2_ files.
Can u please help me out on this.
September 18, 2012 at 6:44 am
AS GSquared said change the OR to an AND and it will work.
In this case 'FON_smp1_D062012_1_' Passes the Second clause as it doesnt have _2_ in the name, the second File passes the First Clause as it doesnt have _1_ in the name,
so using Boolean Logic if you take each clause as a True or False you have
CHARINDEX('_1_','FON_smp1_D062012_1_') = 0 IS FALSE
BUT
CHARINDEX('_2_','FON_smp1_D062012_1_') = 0 IS TRUE
So The over all effect Is True so the Filename can be returned
CHARINDEX('_1_','FON_smp1_D062012_2_') = 0 IS TRUE
BUT
CHARINDEX('_2_','FON_smp1_D062012_2_') = 0 IS FALSE
So The over all effect Is True so the Filename can be returned
If it was an AND statement you need both of the results to be TRUE to return the Filename.
hope this helps.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply