CODEWITHSUNDEEP
mysqlsqlsubstringwhere-clausesql-delete
user3350593
user3350593

Reputation:

Removing a row in mySQl based on the substring of a field value?

I've got a table with 14,000,000 (14 million) rows. I've just realised I can cut this number down significantly by removing rows where a certain field's 1st and 2nd characters are either 23,24,25,54,55, or 56.

I was thinking something along the lines of:

DELETE FROM tablename WHERE Substring of field in column LIKE 24 OR 25 OR 26.... Etc

Thanks,

LazyTotoro :-)

Upvotes: 0

Views: 1183

Answers (3)

Stefan Manciu
Stefan Manciu

Reputation: 500

I think you can use the SUBSTRING() function.

http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php

For example, if you use SUBTRING(str, 1, 2) it will return 2 characters starting from the first position in the string, so if you have SUBSTRING("123456", 1, 2), it will return "12".

So you would have:

DELETE FROM tablename WHERE SUBSTRING(field, 1, 2) IN (24,25,26)

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

If you are sure of which strings to look for, you can just do this:

delete
from tablename
where left(fieldname,2) in ('23','24','25','54','55','56')

left provides a convenient solution since you are looking for the first 2 characters. If you wanted to get a substring between the ends, you could use substring with the syntax substring(fieldname, startingposition, length).

Upvotes: 2

Robert Allan Hennigan Leahy
Robert Allan Hennigan Leahy

Reputation: 6578

MySQL provides the SUBSTR function.

Upvotes: 0

Related Questions