The delete statement SQL-CodePudding

How can I write this?

A delete statement to remove all records from the TableA table where the LastName starts with ba and they have a phone number in the 3 area code.

DELETE
FROM TableA
WHERE LastName = 'ba' AND PhoneNumber = '3';

CodePudding user response：

Assuming the phone number column be text, then we can use LIKE comparisons as follows:

DELETE
FROM TableA
WHERE LastName LIKE 'Ba%' OR LastName LIKE 'ba%' OR PhoneNumber LIKE '3%';

CodePudding user response：

MySQL, SQL Server, PostgreSQL, SQLite are all completely different database products. In all of them though, matching a pattern is done using LIKE, not =. To match a value that starts with a certain prefix you use LIKE 'something%'

% matches any character. It's the equivalent of * in a file search or .* in a regular expression.

DELETE FROM TableA 
WHERE LastName LIKE 'ba%' AND PhoneNumber LIKE '3%'

Different databases have different case-sensitivity (collation) rules though.

In SQL Server and MySQL, case-insensitive sorting and searching is most common, so LIKE 'ba%' will match both Ba and ba.

In PostgreSQL, the norm is case-sensitive matching. You may have to use [Bb]a% in that case, to match either B or b