How can I make this request available?

Question

I need to get rows from my table, outside a field starting with a specific value:

I am currently doing this with a simple query:

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

However, I found out that A.FIELD sometimes contains a different number of spaces before the "WORD".

Obviously, I could re-write the query with a different wildcard, but this will make it unacceptable and slow down its fair bit (this query is executed on a sufficiently large table and should be as efficient as possible).

Is there any way to write a promotion request to solve this problem?

Answer 1

If you cannot clear the data for any reason, one of them is adding a calculated column to the table, which cuts off all the 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 instead find this column:

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

Answer 2

What about ltrim?

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