How can I use contains to look for keyword including a space?

You Can LTRIM RTRIM the column and can get the values.

create table #temp
(
name varchar(50)
)

insert into #temp values(' hertz')

insert into #temp values('hertz ')

insert into #temp values('hertzx')

insert into #temp values('hertz')

select * from #temp where LTRIM(RTRIM(name)) like '%hertz%'

If you want to search if it has space in start or end use below query.

select * from #temp where name like ' %'

select * from #temp where name like '% '

Try with this:

SELECT *
FROM mytable
WHERE name=' Hertz '

If Hertz is just an example, you can use:

SELECT *
FROM mytable
WHERE name like ' % '

which will return all the names with an space in the first and last position

Try as below.

SELECT *
FROM mytable
WHERE name LIKE '% Hertz %'

Learn more about like operator

You could try this

SELECT *
FROM mytable
WHERE name Like '% Hertz %'

To search with spaces included, you need to enclose your expression within double quotes, like :

SELECT *
FROM mytable
WHERE CONTAINS(name, '" Hertz "');

Of course this also can be written with LIKE (although it will be less efficient when dealing with large data sets) :

SELECT *
FROM mytable
WHERE name LIKE '% Hertz %');

If you use like operator, you no need to give spaces between that like below

select * from mytable where name like '%her%'

And also if you want the data of which having spaces, you can use like below

select * from mytable where name like '_%hertz%_'

If we use underscore, it will skip the particular position element and search the data.

Try this

Select * from mytable where replace(name,' ','')='Hertz'

You can also use ltrim & rtrim to remove spaces at run time to match the name values.

You can try this

SELECT a.name
  FROM mytable a
   WHERE a.name like ' %% '

OR

SELECT a.name
      FROM mytable a
       WHERE a.name like' % '

You can find the live demo Demo here