SQL Azure - Substring Searches?

Question

SQL Azure does not support SQL Server's Full Text Search feature. Does this mean a text field cannot be indexed to handle substring searches?

For example, if I have a table Emails, with a Message column And I want to find all messages with both the words 'hello' and 'thanks' in them, will the standard index on the message collumn allow me to do this?

CREATE TABLE Emails (
    [Id] bigint  NOT NULL,
    [Message] nvarchar({some number})  NOT NULL
);
GO

CREATE NONCLUSTERED INDEX Messages_Emails ON Emails


my query (using entity) would look like
var niceMessageQuery = Context.Emails.Where(e => e.Message.Contains("hello") && e.Message.Contains("thanks"));

Is there a better way to setup this query?

Answer 1

Not familiar at all with azure, but is it possible to use a subquery? The inner (sub) query find all records that contain "hello" and the outer query uses that inner query as it's dataset to search for "thanks"?