SQL Server 2012 poor performance when selecting using LIKE from a large table

Question

I have a table with ~1M rows and run the following SQL against it:

select * from E where sys like '%,141,%'

which takes 2-5 seconds to execute (returning ~10 rows), I need it to be 10 times faster at least, is it something which can be achieved with SQL Server 2012?

A sample sys value (sys values length ranges from 5 to 1000 characters):

1,2,3,7,9,10,11,12,14,17,28,29,30,33,35,37,40,41,42,43,44,45,46,47,48,50,51,53,55,63,69,
72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,97,109,110,111,113,117,
119,121,122,123,124,130,131,132,133,134,135,139,141,146

The table's DDL:

CREATE TABLE [dbo].[E](
    [o] [int] NOT NULL,
    [sys] [varchar](8000) NULL,
    [s] [varchar](8000) NULL,
    [eys] [varchar](8000) NULL,
    [e] [varchar](8000) NULL,
 CONSTRAINT [PK_E] PRIMARY KEY CLUSTERED 
(
    [o] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

Your like clause is causing a full table scan.

If you want instant performance for this query, you will need a one-to-many table that contains the following fields:

E_Key  <-- Foreign Key, points to primary key of E table
sys    <-- Each record contains one number, not multiple numbers 
           separated by commas

You can then index sys, and use an ordinary WHERE clause.

Answer 2

If you can't change the table schema you can enable Full-Text search and create a full text index on the table and then do:

select * from E where CONTAINS(sys, ",141,")