Excluding particular words from a string on a WHERE clause

Question

I have a table that stores invoices and I'm trying to get stats out of it, unfortunately the table is poorly build and some crucial information is all mixed up on a nvarchar field, information such as whether the invoice has been cancelled or if part of the charge is exempt resides on this field in a very daliesque string that gets parsed by the frontend. 3453.234;exempt;Invoice Total...

So I want to create a query that would exclude a few words. My problem is how to accomplish a query that can exclude records depending of a list of keywords (cancelled, exempt), so if any of this words is in the field the amount would not be taken in account.

Answer 1

Its not too difficult to build a query that does it. First create a query that joins on a table using LIKE with wildcards. Then exclude everything from this query.

To see what I mean, see this simple example: http://sqlfiddle.com/#!6/619fb/2

Or alternately I have reproduced an example here:

--Create tables for comparing data
CREATE TABLE Invoice
(
  InvoiceID INT NOT NULL IDENTITY,
  InvoiceData varchar(200) NOT NULL,
);

CREATE TABLE BadWords
(
  BadWordID INT NOT NULL IDENTITY,
  BadWord varchar(10) NOT NULL
);

--Insert data
INSERT INTO Invoice (InvoiceData)
VALUES ('This is some invoice data'), ('it is about'), ('something interesting that'),
('you should look at'), ('because its got invoice information');

INSERT INTO BadWords (BadWord)
VALUES ('this'),('invoice');

--Test query:
SELECT *
FROM Invoice
WHERE InvoiceID NOT IN (
  SELECT InvoiceID
  FROM Invoice i
  INNER JOIN BadWords b ON i.InvoiceData LIKE '%' + b.BadWord + '%'
)

Performance may be an issue with this if you have hundreds of thousands of rows. Without more information though it would be difficult to give you a high performing query (for example using a NOT CONTAINS free text query may give better performance).

Answer 2

Full text search is a richer way of indexing complex strings. I know your case does not contain prose but FTS may still work for you.

As an alternative to wildcard search you could split your concatenated values into their separeate parts. There are many ways to achieve this; reading this link will lead you to one that suits. Then you can treat your data in a set-wise manner. This assumes the positions within the nvarchar are consistent, or at least well-defined.

Either way you are likely to end up with table scans each time you run the query as a) the parser will not use an index if there's a leading wildcard in the predicate or b) you have to split the values before you can filter on them.

If you could somehow manage to index a view that includes the split-out values, that may perform adequately.

Answer 3

select * 
from invoice 
where invoicedata not like '%invoice%' 
or invoicedata not like '%this%'