Solved

deleting SQL records based off ending characters

Posted on 2013-01-08
6
266 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
Undo Button 1 32
SSMA Access BE Linked Table Migration Linking Issue 2 41
Access 2010 7 42
Display Excel gridlines when called from Access 7 13
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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