Friday, February 24, 2012

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

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

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

|||

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)

|||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.

No comments:

Post a Comment