0

Some background, I have a code column that is char(6). In this field, I have the values of 0,00,000,0000,000000,000000. It seems illogical but that's how it is. What i need to do is delete all rows that possess these code values. I know how to do it individually as such

delete from [dbo.table] where code='0'
delete from [dbo.table] where code='00'

and so on.

How does one do this one section of code instead of 6

Improve this question
1
  • 1
    It looks like there's an answer for all 3 options (IN/OR, REGEXP, and CONVERT to int), so I won't bother putting this into an answer. But keep in mind that if you have indexed this code column, only the IN/OR answer will take advantage of the index. CONVERT (or pretty well any function) will prevent the index from being referenced, as well REGEXP. Commented Sep 5, 2017 at 21:56

3 Answers 3

Reset to default
2

Try this:

delete from [dbo.table] where code='0'
                           or code='00'
                           or code='000'

etc. You get the idea.

There can be more efficient ways when the set of vales gets larger, but your 5 or 6 values is still quite a ways from that.

Update:

If your list grows long, or if your table is significantly larger than can reside in cache, you will likely see a significant performance gain by storing your selection values into an indexed temporary table and joining to it.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Consider IN() clause.
@Parfait. So something like this, "delete from [dbo.table] where in(code='0','00', and so on)?
@TimWilcox: delete from [dbo.table] where code in ('0', '00', '000', '0000', '00000', '000000')
@RToyota: Unless there has been a change in recent SQL Server versions, IN is always implemented as a sequential OR; thus five values means five passes of the data, possibly indexed. If NOT indexed, that is as many complete table scans as values. That's fine for short lists, but for larger lists JOINING against an indexed temporary table will be much faster.
@RToyota: Thank you for challenging me on that - I seem to be out of date. I was unable to replicate this (traditionally well known) behaviour on a SQL 2016 instance. The problem with having been around almost forever, is that not everything I know remains valid. ;-) Memory suggests that this optimization must be more recent than 2008 or 2008 R2. The point to not using IN was to be explicit about the performance difficulties - but that no longer applies.
|
1

It strongly depends on your DBMS, but I suggest to use regular expressions. For example, with MySQL you just need simple query like this:

delete from dbo.table where code regexp '(0+)'

For most of popular DBMS you can do the same, but syntax may be various

Improve this answer

5 Comments

Why the downvote? Regex is a valid way to solve this problem.
I definitely did not downvote anybody. The answers from this question have been outstanding.
Not to sound naive but do I not have to specify a column is this regex item? I have other columns that could have a zero value in them.
@TimWilcox, my mistake, sorry. Of course, you need to specify column name, see the updated answer
What is the performance for this use of DOT NET code, with consequent context switching, on a large table? Seems like a table scan is needed since the criteria is a non_SARG, with two martialling context-switches for every row. Looks really sucky to me.
0

I can't test it right now, but the following should work:

DELETE FROM dbo.table WHERE CONVERT(int, code) = 0

edit- Just thought of another way, that should be safer:

DELETE FROM dbo.table WHERE LEN(code) > 0 AND LEFT(code + '0000000000', 10) = '0000000000'
Improve this answer

4 Comments

I will test it out in just a few.
MySQL, for example, returns 0 when you try to cast e.g. '0a0', so I think this is potentially dangerous approach. Try select cast('0a0' as unsigned). On the other engines the problem most likely occures too
This is a clever way to do it. @OlegRybalchenko brings up a good point, but on top of that, you should keep in mind that the code column might contain non-int values as well. Also, CONVERT() will prevent the use of an index on the code column.
@zambonee A small suggestion regarding your edit about padding: the values can be reduced to use 6 chars instead of 10 (the OP's column is char(6)). Also, if you only pad it with 5 zeros, you eliminate the need to do a LEN check (because if it is blank, and only padded with 5 zeros, it will not match the 6 zero string). This may be simpler: DELETE FROM dbo.table WHERE LEFT(code + '00000', 6) = '000000'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.