Solved

deleting SQL records based off ending characters

Posted on 2013-01-08
6
274 Views
Last Modified: 2013-01-08
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!
0
Comment
Question by:mrosier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38755010
Use a wildcard:

DELETE * FROM YourTable WHERE YourField LIKE "*000"

Open in new window

0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 38755019
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
 

Author Comment

by:mrosier
ID: 38755024
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 77

Expert Comment

by:peter57r
ID: 38755032
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38755033
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
 

Author Closing Comment

by:mrosier
ID: 38755063
excellent
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question