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!
mrosierAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Use a wildcard:

DELETE * FROM YourTable WHERE YourField LIKE "*000"

Open in new window

0
 
mbizupConnect With a Mentor Commented:
The above will work with an Access interface.  If you are doing this  strictly in a SQL Server environment, use % instead of * for your wildcard:

DELETE * FROM YourTable WHERE YourField LIKE "%000"

Open in new window

0
 
mrosierAuthor Commented:
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?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
peter57rCommented:
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"
0
 
mbizupCommented:
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

0
 
mrosierAuthor Commented:
excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.