1

I have got a sample query with the output in the screenshot below.

Sample Query and its Output enter image description here

But I am supposed to get the same ouput with the query written in a single line using "Like" condition. I have tried my best and also attached what I have tried below:

Screenshot of Query Tried

enter image description here

Geetha
  • 19
  • 3

4 Answers4

1

Not totally sure what the parameters of your assignment are, but this will work to produce the same results. 

Select *
From City
Where City like '%Salem%' 
or City like '%Chennai%' 
or City like '%Bangalore%'
APH
  • 4,109
  • 1
  • 25
  • 36
0

Try With IN OPERATOR :-

Select *
From City
Where City IN ('Salem','Chennai','Banglore')
Saidul Haque
  • 1
  • 3
0

Try like this,

SELECT *
FROM (
    VALUES ('Chennai')
        ,('Chennai,Bangalore')
        ,('Bangalore')
        ,('Bangalore,Salem')
        ,('Salem')
        ,('Covai,Salem')
        ,('Chennai,Covai,Salem')
        ,('Chennai,Bangalore,Covai,Salem')
    ) T(city)
WHERE city LIKE '%'
    OR city LIKE '%' + REPLACE(city, ',', '%')+'%'
StackUser
  • 5,370
  • 2
  • 24
  • 44
0

You would have to use multiple OR conditions as mentioned in one of the answers or you can do this

WITH data as
(
SELECT'%Salem%' as city
union all
SELECT'%Chennai%' as city
union all
SELECT'%Bangalore%' as city
)

SELECT DISTINCT c.* FROM city c INNER JOIN data d ON c.city like d.city
Sam
  • 2,935
  • 1
  • 19
  • 26
  • Almost the same, consider the fictive city 'Salem Bangalore'. (Or York vs New York.) – jarlh May 10 '16 at 07:20
  • I have added `DISTINCT` now it won't return any duplicate rows, If that's what you were pointing out. @jarlh – Sam May 10 '16 at 07:51