Link to home
Start Free TrialLog in
Avatar of mrosier
mrosierFlag for United States of America

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mrosier

ASKER

and the same query could be used conversely if I was interested in the beginning of that value? i.e. "111*" would deletey anything with three leading 1s?
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"
To begin with three zeros, just place the * or % at the end:
Access interface:
DELETE * FROM YourTable WHERE YourField LIKE "000*"

Open in new window



SQL Server environment:

DELETE * FROM YourTable WHERE YourField LIKE "000%"

Open in new window

Avatar of mrosier

ASKER

excellent