Last post Aug 09, 2016 09:10 AM by techviju
Jul 29, 2016 01:43 PM|Santhosh ASP ocean|LINK
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??
Jul 29, 2016 02:36 PM|deepalgorithm|LINK
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.
Jul 29, 2016 03:07 PM|Santhosh ASP ocean|LINK
Sorry.. I did not understand what u just told.
Can we do that operation without 'like' or not?
Jul 29, 2016 03:34 PM|TechView|LINK
Jul 29, 2016 03:39 PM|PatriceSc|LINK
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
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...).
Jul 29, 2016 04:38 PM|deepalgorithm|LINK
Read my answer. You need to enable Full Text Search.
Review the following from Microsoft.
Aug 04, 2016 07:41 PM|limno|LINK
SELECT * from tblEmployee
Aug 05, 2016 06:12 AM|eralper|LINK
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
Aug 08, 2016 09:45 AM|sandeepmittal11|LINK
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
Aug 09, 2016 09:10 AM|techviju|LINK
use below query
SELECT * from tblEmployee where CHARINDEX('S',Ename)=1