Using Subquery for the content for LIKE clause in SQLserver

Question

0.00/5 (No votes)

See more:

Hi,

I have a query

SQL

Select * from CityTable where CountryId="somevalue" and CityName LIKE '%andhra%'

.

Instead of this i would like to use the output of a subquery to like claues

SQL

Select * from CityTable where CountryId="somevalue" and CityName LIKE '%
 here I want to use  a query(select cityname from Allcity where CityID='1')%'.

How can I use.

Posted 30-Jul-12 20:35pm

Velkumar Kannan

Updated 30-Jul-12 20:59pm

Mohamed Mitwalli

v2

Add a Solution

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)

_Amy · Accepted Answer · 2012-07-30T20:44:00

Solution 2

Hi,
Try this:

SQL

SELECT * FROM CityTable
WHERE CountryId="IND" 
AND CityName LIKE
'%'+(SELECT CityName FROM Allcity WHERE CityID='1')+'%'

--Amit

Posted 30-Jul-12 20:44pm

_Amy

kirthiga S · Accepted Answer · 2012-07-31T00:53:00

Solution 3

Hi Velkumar Kannan,

If your subquery returns single value you can use this query

SQL

Select * from CityTable where CountryId="somevalue" and 
CityName like '%'+ (select cityname from Allcity where CityID='1')+ '%'

Posted 31-Jul-12 0:53am

kirthiga S

Comments

Pablo Aliskevicius 31-Jul-12 7:37am

NO YOU CANT!
If the subquery returns AT MOST one record, it may return zero records, which would generate an error.
If you know (in client code) that one and only one record will be returned, you also know the name of the city.
Best wishes,
Pablo.

Pablo Aliskevicius · Accepted Answer · 2012-07-30T20:40:00

Solution 1

SQL

Select * from CityTable 
where CountryId="somevalue" 
and CityName LIKE '%' + (select max(cityname) from Allcity where CityID='1') + '%'

The LIKE operator receives an expression, not necesarily a hard-coded constant.

Hope this helps,
Pablo.

Posted 30-Jul-12 20:40pm

Pablo Aliskevicius

Comments

_Amy 31-Jul-12 6:59am

max(cityname) strange.. It'll return the value whose ASCII value will be maximum..

Pablo Aliskevicius 31-Jul-12 7:34am

If the subquery returns multiple rows, or zero rows, you're in a pickle. Using max() lets SQL Server know that there will be one and only one result.
If there are zero rows, max() will return null, which stays null after concatenation, and the outer query will return zero rows.
Otherwise, LIKE '%' + (zero rows) + '%' will generate an error.

_Amy 31-Jul-12 7:37am

Good logic. :) I din't downvoted..