how to use like and substring in where clause in sql

Question

Hope one can help me and explain this query for me, why the first query return result but the second does not:

EDIT: first query:

select name from Items where name like '%abc%'

second Query:

 select name from Items where name like substring('''%abc%''',1,10)

why the first return result but the second return nothing while

substring('''%abc%''',1,10)='%abc%'

If there are a logic behind that, Is there another approach to do something like the second query,

my porpuse is to transform a string like '''abc''' to 'abc' in order to use like statement,

Answer 1

You can concatenate strings to form your LIKE string. To trim the first 3 and last 3 characters from a string use the SUBSTRING and LEN functions. The following example assumes your match string is called @input and starts and ends with 3 quote marks that need to be removed to find a match:

select name from Items where name like '%' + SUBSTRING(@input, 3, LEN(@input) - 4) + '%'