SQL Server Like Operator

Question

User-1940827647 posted

Hi everyone..

I have a tblEmployee table with Eid,Ename,Salary,Did columns.

I want to get Ename that contains 'S'.

I have used a query i.e       SELECT * from tblEmployee where Ename like '%S%'

In an interview they asked me to do it without like operator.

Can anyone please tell me, how to do it??

Answer 1

User765422875 posted

Starting a like with a % is going to cause a table scan as it has to evaluate every value.  If you index the column it should be an index (rather than table) scan. Charindex and patindex are alternatives, but will still scan.

Bottom line - the like operator isn't very efficient at searching text when you're checking for the existence of a string within text data. Full Text Search (FTS) technology was designed to address these shortcomings.

Answer 2

User-1940827647 posted

Sorry.. I did not understand what u just told.

Can we do that operation without 'like' or not?

Answer 3

User-698989805 posted
Hello Santhosh! You can do it without using LIKE operator:

SELECT * FROM table_name WHERE CONTAINS(column_name, '"*value*"');

In this case, you'll need full text indexing. Check the link:

https://msdn.microsoft.com/en-us/library/cc879306(v=sql.105).aspx

Answer 4

User753101303 posted

Yes. http://forums.asp.net/members/deepalgorithm.aspx meant that you can use :

- full-text search: https://msdn.microsoft.com/en-us/library/ms142571.aspx allows to do that and more if it suit the scenario
- maybe https://technet.microsoft.com/en-us/library/ms190184(v=sql.105).aspx (CHARINDEX or PATINDEX) but it would be quite theorical (it returns the position so you could still test if a S is found in a column but in practice nobody would use that rather than LIKE)

I would have likely added that in this particular case, LIKE still seems the best option (full text search is better suited to long description and patterns such as a word near another word in some text etc...).

Answer 5

User765422875 posted

Read my answer. You need to enable Full Text Search.

Review the following from Microsoft.

https://msdn.microsoft.com/en-us/library/ms142571.aspx

Answer 6

User77042963 posted

 SELECT * from tblEmployee

where Len(Ename)<>Len(Replace(Ename,'S','')) 

Answer 7

User-595703101 posted

You can use patindex SQL string function as shown below

SELECT * from tblEmployee where PATINDEX('%S%', Ename) > 0

I use patindex function to fetch numeric values from string expressions

Answer 8

User-62323503 posted

There are several options to find out the same. Some are already suggested by other members. Another one is using charindex

SELECT * from tblEmployee where CHARINDEX('S', Ename) > 0

Answer 9

User-2017229834 posted

use below query

SELECT * from tblEmployee where CHARINDEX('S',Ename)=1 

thanks 

Vijay