Doubt on Sql Server in query

Question

0.00/5 (No votes)

See more:

Hi Everyone,
Very Good Evening
Domain: SQL Server

Question:
How to retrieve (or) filter data from below query without using LIKE operator?

SQL

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

Karruksen

Add a Solution

Comments

[no name] 27-Mar-13 7:59am

Did you try anything? Did you try IN?

Karruksen 27-Mar-13 8:18am

i tried but so far not coming answer..

Karruksen 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....

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2013-03-27T01:59:00

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.

Posted 27-Mar-13 1:59am

OriginalGriff

Comments

Shanalal Kasim 27-Mar-13 8:08am

+5

Karruksen 27-Mar-13 8:18am

i used but answer is not coming...(patindex,charindex,left,right)

**Zoltán Zörgő** · Answer 2 · 2013-03-27T02:01:00

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

Posted 27-Mar-13 2:01am

Zoltán Zörgő

Comments

Karruksen 27-Mar-13 8:04am

Sir, it's one of my interview question that why i ask..

Zoltán Zörgő 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 CHARINDEX function
But you have to know, that like is the simplest and best approach for such a task.

Shanalal Kasim 27-Mar-13 8:08am

+5

Zoltán Zörgő 27-Mar-13 8:11am

Thank you

Karruksen 27-Mar-13 8:15am

answer is not coming by means of patindex,charindex,right,left

Zoltán Zörgő 27-Mar-13 8:24am

I don't understand this comment.

Karruksen 27-Mar-13 8:46am

i used above format(CHARINDEX,PATINDEX..)So far i can't find any answer sir...

Zoltán Zörgő 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?

josh-jw · Answer 3 · 2013-03-27T02:04:00

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

SQL

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

Posted 27-Mar-13 2:04am

josh-jw

Updated 27-Mar-13 2:19am

v3

Comments

Karruksen 27-Mar-13 8:12am

Hello Sir,I want only without using LIKE operator. Is it possible?

josh-jw 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 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 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 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