Delete row where a particular field value is not in a list

Transact-SQL

ctallos


I have a table which has a field called filename, thus the data in the field would look something like this:

FileName.doc
FileName.txt
FileName.foo

I need to delete several rows from this table where the filename field DOES NOT contain the following extensions (e.g. row with FileName.foo would be deleted):

.txt
.pdf
.rtf
.doc
.htm
.html
.mht
.mhtml
.dot
.wk1
.wk3
.wk4
.xls
.xlw
.asc
.olk
.pab
.scd
.ans
.wri
.mcw
.wpd
.wps
.ppt
.pps
.xls
.xlt
.xlw

Top

Re: Delete row where a particular field value is not in a list

Konstantin Kosinsky


Crazy method for identifing file extension:

declare @filename varchar(100)

set @filename= 'filename.txt'

select charindex('.',@filename,-1) --Position of last dot in @filename

select substring(@filename,charindex('.',@filename,-1),len(@filename)-charindex('.',@filename,-1)+1)

--result '.txt'

Than you could use "IN keyword"

PS. With query works very slow



Top

Re: Delete row where a particular field value is not in a list

Mark - SQL

create table mytable(filename varchar(20))
insert into mytable(filename) values('FileName.doc')
insert into mytable(filename) values('FileName.txt')
insert into mytable(filename) values('FileName.foo')

create table allowed(ext varchar(8))
insert into allowed(ext)
select '.txt' union all
select '.pdf' union all
select '.rtf' union all
select '.doc' union all
select '.htm' union all
select '.html' union all
select '.mht' union all
select '.mhtml' -- etc

delete from mytable
where not exists(select * from allowed where filename like '%'+ext)



Top

Re: Delete row where a particular field value is not in a list

ctallos

Marks answer seems most appropriate because it allows me to keep a table of "allowable" extensions. But I am open to other ideas if anyone else wants to contribute.


Top