if/then - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

if/then

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-11-15 : 20:49:36

directory filename
/usr5/released.Z/5017902_c_01.Z 5016973.prt.1
/usr5/released.Z/5017902_c_01.Z 5016975.prt.1
/usr5/released.Z/5017902_c_01.Z 5017902.asm.1
/usr5/released.Z/5001170_b_01.Z 5001170.prt.1
/usr5/released.Z/5001222_b_01.Z 5001255.prt.1

logic:
if filename like '%.asm%'
then filter out all of the records in directory with the same name

I want it to return:
/usr5/released.Z/5001170_b_01.Z 5001170.prt.1
/usr5/released.Z/5001222_b_01.Z 5001255.prt.1

How can I do this in SQL?

Thanks,
Lane

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-15 : 21:18:03

select *
from table
where filename not like '%.asm%'

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-15 : 22:18:15

SELECT B.Directory, B.[FileName]
FROM dbo.FILES B
WHERE
B.Directory NOT IN ( SELECT A.Directory
FROM dbo.FILES A
WHERE PATINDEX ( '%.asm%' , A.[FileName] ) > 0)

OR

SELECT A.Directory, A.FileName
FROM FILES A LEFT JOIN (SELECT Directory From FILES WHERE PATINDEX ( '%.asm%' , [FILENAME] ) > 0) B
ON A.Directory = B.Directory
WHERE B.Directory IS NULL

Edited by - ValterBorges on 11/15/2002 22:38:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources