Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi Everyone,
Very Good Evening
Domain: SQL Server
 

Question:
How to retrieve (or) filter data from below query without using LIKE operator?
 
create table stu(sname varchar(50))
 
insert into stu values('karthick'),('kannan'),('suresh'),('rajesh'),('mahesh'),('muthu'),('gopal')
select * from stu
 
select sname from stu where sname like '%esh%'
 
Solve this Problem..
 
Thanks & Regards
Karthick
Posted 27-Mar-13 1:54am
Comments
ThePhantomUpvoter at 27-Mar-13 7:59am
   
Did you try anything? Did you try IN?
Karruksen at 27-Mar-13 8:18am
   
i tried but so far not coming answer..
Karruksen at 27-Mar-13 8:49am
   
gvprabu sir pls find answer for above problem.. i want answer without using LIKE operator..what is equal to LIKE in sql? i want to know that pls help me....
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You could use a full text search: MSDN[^] but it's probably overkill for such a simple job, that looks tailor made for LIKE from what you show.
  Permalink  
Comments
Shanalal Kasim at 27-Mar-13 8:08am
   
+5
Karruksen at 27-Mar-13 8:18am
   
i used but answer is not coming...(patindex,charindex,left,right)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

1) What's wrong with like?
2) If you can't use this operator for whatever reason, you probably need to redesign the whole thing
3) You could use regular expressions, but it is not supported by default, and it is much more resource-expensive than like
4) You can use PATINDEX[^] function, but I think it is even a little bit more complicated than using like
5) In you samples all "esh" are at the end of the string, if it is true in all cases, you can use RIGHT[^] function
  Permalink  
Comments
Karruksen at 27-Mar-13 8:04am
   
Sir, it's one of my interview question that why i ask..
Zoltán Zörgő at 27-Mar-13 8:11am
   
Well than you have to gather all alternatives. Plus the ones we missed :) Like these for example: - you can make a CLR UDF to do the comparison in managed code - you can use <a href="http://msdn.microsoft.com/en-us/library/ms186323.aspx">CHARINDEX</a> function But you have to know, that like is the simplest and best approach for such a task.
Shanalal Kasim at 27-Mar-13 8:08am
   
+5
Zoltán Zörgő at 27-Mar-13 8:11am
   
Thank you
Karruksen at 27-Mar-13 8:15am
   
answer is not coming by means of patindex,charindex,right,left
Zoltán Zörgő at 27-Mar-13 8:24am
   
I don't understand this comment.
Karruksen at 27-Mar-13 8:46am
   
i used above format(CHARINDEX,PATINDEX..)So far i can't find any answer sir...
Zoltán Zörgő at 27-Mar-13 11:13am
   
What kind of answer? You don't know how to solve the task by these means? Or there is somebody rejecting these?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

like operator is the simplest way ,you can retrieve the records compare with other functions.
check this link
 
http://www.w3schools.com/sql/sql_like.asp[^]
 
otherwise
 
You can use CONTAINS
 SELECT * FROM table_name WHERE CONTAINS(column_name , '"*value*"') 
check this link for more info
http://msdn.microsoft.com/en-us/library/ms187787%28v=sql.105%29.aspx
  Permalink  
v3
Comments
Karruksen at 27-Mar-13 8:12am
   
Hello Sir,I want only without using LIKE operator. Is it possible?
josh-jw at 27-Mar-13 8:22am
   
You can use CONTAINS SELECT * FROM table_name WHERE CONTAINS(column_name , '"*value*"') check this link for more info http://msdn.microsoft.com/en-us/library/ms187787%28v=sql.105%29.aspx For CONTAINS you need to create a FULL TEXT INDEX on the table.
Karruksen at 27-Mar-13 8:40am
   
"Msg 7601, Level 16, State 2, Line 1 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'stu' because it is not full-text indexed." How to use in Value part ?
Karruksen at 27-Mar-13 8:43am
   
How to search a word?If i want mahesh,rajesh,suresh from table how to i use that query?pls solve for my question relavant answer..
josh-jw at 27-Mar-13 8:55am
   
you have to create a full-text indexed on ur table check this link http://msdn.microsoft.com/en-us/library/ms187317%28v=sql.105%29.aspx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 838
1 OriginalGriff 461
2 Tadit Dash 355
3 sanket saxena 329
4 Peter Leow 228
0 Sergey Alexandrovich Kryukov 11,990
1 OriginalGriff 7,349
2 Peter Leow 5,003
3 Abhinav S 4,003
4 Maciej Los 3,575


Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 27 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid