How can I make this query sargable?

Question

I need to retrieve rows from my table with out a field starting with a certain value:

I'm currently doing so with a simple query like this:

SELECT A.ID FROM SCHEMA.TABLE A WHERE A.FIELD NOT LIKE 'WORD%'

However, I have learned that A.FIELD sometimes contains a varying number of blank spaces before "WORD".

Obviously, I could re-write the query with another wildcard, but that would make it non-sargable and slow it down a fair bit (this query runs on a reasonably large table and needs to be as efficient as possible).

Is there any way I can write a sargable query to fix this problem?

Answer 1

If you can't clean the data for any reason, one option is to add a computed column to your table that trims all leading and trailing spaces:

ALTER TABLE YourTable
    ADD TrimmedYourColumn as (RTRIM(LTRIM(YourColumn)))

And index the computed column:

CREATE INDEX IX_YourTable_TrimmedYourColumn 
    ON YourTable (TrimmedYourColumn)

And now search that column instead:

SELECT A.ID FROM YourTable A WHERE TrimmedYourColumn NOT LIKE 'WORD%'