Solved

deleting SQL records based off ending characters

Posted on 2013-01-08
6
253 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 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