mrosier
asked on
deleting SQL records based off ending characters
Hi Folks! I need to delete records from a SQL table using the ending characters within a column's values. For example, let's say I have table1 with column1. My logic says I want to delete * from table1 where .... and I want to make the condition say where the ending characters in the column1 cells are 000 thereby deleting all records from the table who have values in column1 that all end with three 0s at the end of the cell value. Anyone know how to write this? Also, conversely, how would I write this same query if I wanted to say values that begin with three 0s in case that comes up as well? I am doing these in Access FYI. Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Values that begin with 000 must be text so you can use...
Delete * from table where left(column1fieldname,3) = "000"
There is no going back if you delete by mistake. Make sure you really want to do it.
Assuming a text field agian then you can use this for the last 3 characters..
Delete * from table where right(column1fieldname,3) = "000"
Delete * from table where left(column1fieldname,3) = "000"
There is no going back if you delete by mistake. Make sure you really want to do it.
Assuming a text field agian then you can use this for the last 3 characters..
Delete * from table where right(column1fieldname,3) = "000"
To begin with three zeros, just place the * or % at the end:
Access interface:
SQL Server environment:
Access interface:
DELETE * FROM YourTable WHERE YourField LIKE "000*"
SQL Server environment:
DELETE * FROM YourTable WHERE YourField LIKE "000%"
ASKER
excellent
ASKER