I've the next 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, basically were using RegEx, I would bunch all of the Nots into one large (gettingcontractingstopping), but I am unsure how to get this done in SQL.
It is possible to method to render this question more legibly/stylishly?
Simply by getting rid of redundant stuff and taking advantage of a regular 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 discuss regular expressions. Some DBMS will have it: MySQL, Oracle... However, the option of either syntax should consider the execution plan from the query: "how quick it'sInch instead of "how nice it appearsInch.
With MySQL, you are able to utilize regular expression where-clause parameters:
SELECT something FROM table WHERE column REGEXP 'regexp'
Therefore if that is what you are using, you can write a normal expression string that's possibly a little smaller sized that the 4
like criteria. It might be more difficult to determine exactly what the totally doing for some individuals, however.
It appears like SQL Server offers a similar feature.
Sinec it may sound like you are building this along the way to mine your computer data, here's something 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're then always just running exactly the same query and you will simply change what's within the Includes and Excludes tables to refine your searches.
Based on what SQL server you're using, it might support REGEX itself. For instance, google searches reveal that SQL Server, Oracle, and mysql all support regex.
You can push all of your negative criteria right into a short circuiting Situation expression (works Sql Server, unsure about MSAccess).
SELECT * FROM phrases WHERE phrase LIKE '%ing aids%' AND CASE WHEN phrase LIKE '%getting%' THEN 2 WHEN phrase LIKE '%contracting%' THEN 2 WHEN phrase LIKE '%preventing%' THEN 2 ELSE 1 END = 1
Around the "more effectiveInch side, you have to find some criteria that enables you to definitely avoid reading through the whole Phrases column. Double on the sides wildcard criteria isn't good. Right on the sides wildcard criteria is nice.