Home Ask Login Register

Developers Planet

Your answer is one click away!

Drake February 2016

Selecting Multiple values from one string in SQL

Hi All I am using Oracle 11. The requirement is to select only those records where we have the 'New York' and 'Dallas'. Please note that the order of the cities with the same record can change. so from the following record set i should only have two records (i.e. Rec # 3 and 5). Please note that the values coming in City are concatenated by using wm_concat function

Rec#    City
1   New York
2   Huston
3   New York;Dallas
4   Los Angles
5   Los Vegas;Dallas;San Dieago;New York
6   Huston;Chicago;Salt Lake City;Cleaveland


tokamak February 2016

I don't know Oracle syntax, but something like this:

Select columns where cities like "Dallas" and cities like "New York"

dantella February 2016

Try this

 SELECT * from TABLE_NAME where City like '%New York%Dallas%' or City like '%Dallas%New York%'

Juan Carlos Oropeza February 2016

I wrote it on Postgres because oracle fiddle is wrong but should be the same

SQL Fiddle Demo

FROM cities
WHERE "City" LIKE '%New York%'
AND "City" LIKE '%Dallas%'

The problem is if you try search something like 'York' can match partially and will also find 'New York' so you can fix like this

FROM cities
WHERE "City" LIKE '%York%'; -- will find `New York`

FROM cities
WHERE ';' || "City" || ';' LIKE '%;York%;'; -- correct solve

As @Egor suggest you can do it with a single LIKE

FROM cities
WHERE ';' || "City" || ';;' || "City" || ';' LIKE '%;Dallas;%;New York;%' ;

Gary_W February 2016

Try with REGEXP_LIKE where the city sought after could be at the start or end of the line also. The regular expression means where the sought after city is proceeded by the start of the line or the delimiter and is followed by the delimiter or the end of the line.

with tbl(rec, city) as (
  select 1, 'New York' from dual union
  select 2, 'Huston' from dual union
  select 3, 'New York;Dallas' from dual union
  select 4, 'Los Angles' from dual union
  select 5, 'Los Vegas;Dallas;San Dieago;New York' from dual union
  select 6, 'Huston;Chicago;Salt Lake City;Cleaveland' from dual union
  select 7, 'Huston;Chicago;Salt Lake City;New York;York' from dual union
  select 8, 'York;Huston;Chicago;Salt Lake City' from dual  
select rec, city
from tbl
where regexp_like(city, '(^|;)York(;|$)')
and   regexp_like(city, '(^|;)New York(;|$)');

Post Status

Asked in February 2016
Viewed 3,219 times
Voted 4
Answered 4 times


Leave an answer

Quote of the day: live life