4

I want to use contains with search a word that has single space on left and right side. But it is not working..

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

Any help is appreciated..

geisterfurz007
  • 5,292
  • 5
  • 33
  • 54
Janna
  • 41
  • 2
  • I'm not so sure you can use `CONTAINS` to search for ending characters. You can search for prefixes with the wildcard * (`CONTAINS(name, '" *"Hertz')`). You might need to add an additional clause to validate that the last char is a space. – EzLo Jan 03 '19 at 10:06
  • 2
    To all the people answering, the OP is asking for `CONTAINS` which uses a full text index and behaves different than `LIKE`. – EzLo Jan 03 '19 at 10:09
  • You can write where condition as - `WHERE a.name like ' %% '` Check my answer. – Suraj Kumar Jan 03 '19 at 10:34
  • @Janna you can try your luck posting on dba.stackexchange.com – EzLo Jan 03 '19 at 11:27

8 Answers8

2

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 '% '
sandeep sharma
  • 70
  • 5
0

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

James
  • 2,954
  • 2
  • 12
  • 25
0

Try as below.

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

Learn more about like operator

Karan
  • 12,059
  • 3
  • 24
  • 40
0

You could try this

SELECT *
FROM mytable
WHERE name Like '% Hertz %'
S.Jose
  • 216
  • 1
  • 7
0

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 %');
GMB
  • 216,147
  • 25
  • 84
  • 135
0

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.

sri harsha
  • 676
  • 6
  • 16
0

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.

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Deependra Bhandari
  • 51
  • 2
0

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

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42