Make an SQL request more efficient and tidy?

Question

I have the following SQL query:

SELECT Phrases.*
FROM Phrases
WHERE (((Phrases.phrase) Like "*ing aids*") 
  AND ((Phrases.phrase) Not Like "*getting*") 
  AND ((Phrases.phrase) Not Like "*contracting*")) 
  AND ((Phrases.phrase) Not Like "*preventing*"); //(etc.)

Now, if I were using RegEx, I might bunch all the Nots into one big (getting|contracting|preventing), but I'm not sure how to do this in SQL.

Is there a way to render this query more legibly/elegantly?

Answer 1

Depending on what SQL server you are using, it may support REGEX itself. For example, google searches show that SQL Server, Oracle, and mysql all support regex.

Answer 2

Just by removing redundant stuff and using a consistent naming convention your SQL looks way cooler:

SELECT *
FROM phrases
WHERE phrase     LIKE '%ing aids%'
  AND phrase NOT LIKE '%getting%'
  AND phrase NOT LIKE '%contracting%' 
  AND phrase NOT LIKE '%preventing%'

You talk about regular expressions. Some DBMS do have it: MySQL, Oracle... However, the choice of either syntax should take into account the execution plan of the query: "how quick it is" rather than "how nice it looks".

Answer 3

With MySQL, you're able to use regular expression where-clause parameters:

SELECT something FROM table WHERE column REGEXP 'regexp'

So if that's what you're using, you could write a regular expression string that is possibly a bit more compact that your 4 like criteria. It may not be as easy to see what the query is doing for other people, however.

It looks like SQL Server offers a similar feature.

Answer 4

Sinec it sounds like you're building this as you go to mine your data, here's something that you could consider:

CREATE TABLE Includes (phrase VARCHAR(50) NOT NULL)
CREATE TABLE Excludes (phrase VARCHAR(50) NOT NULL)

INSERT INTO Includes VALUES ('%ing aids%')
INSERT INTO Excludes VALUES ('%getting%')
INSERT INTO Excludes VALUES ('%contracting%')
INSERT INTO Excludes VALUES ('%preventing%')

SELECT
     *
FROM
     Phrases P
WHERE
     EXISTS (SELECT * FROM Includes I WHERE P.phrase LIKE I.phrase) AND
     NOT EXISTS (SELECT * FROM Excludes E WHERE P.phrase LIKE E.phrase)

You are then always just running the same query and you can simply change what's in the Includes and Excludes tables to refine your searches.