We help IT Professionals succeed at work.

delete records not working correctly in transact sql

I have a table with a column named filename. I am trying to delete all of my records that start with a "9-" except not delete records that start with "9-5400". This statement doesn't work:

delete from tblmaster where substring(filename,1,2) = '9-' and substring(filename,1,6) != '9-5400'

Can someone help me rephrase this string so it ignores the 9-5400 but deletes everything else that starts with 9-.

Thanks!
Comment
Watch Question

SQL Server DBA
Top Expert 2011
Commented:
delete from tblmaster where LEFT(Filename,2) = '9-' and LEFT(Filename,6) != '9-5400'
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Try this then replace select with delete

select * from tblmaster
where substring(ltrim(filename),1,2) = '9-' and substring(ltrim(filename),1,6) != '9-5400'

Commented:
A little modification on previous comment (credits!),
Always include the 'modifications' you do in the select list.  And if you don't get what you want, one by one comment out the filter until you see where the problem is.
As Icohan already suggested there could be a space before the filename

select substring(filename,1,2),substring(filename,1,6), *
from tblmaster
where substring(ltrim(filename),1,2) = '9-'
and substring(ltrim(filename),1,6) != '9-5400'


And before trying a delete .... always try it as a select first so that you can verify it is realy what you want.

Commented:
But I would write it as a LIKE, but it won't change the outcome.

where filename like '9-%'
and filename not like '9-5400%'
Bob ButcheriSeries Engineer

Author

Commented:
Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.