multiple selection in single query oracle

Question

CREATE TABLE CENSUS_MSA_DROPDOWN (
  OID  number(15),
  CityName  VARCHAR(60) CONSTRAINT census_msa_dropdown_pk PRIMARY KEY,
  StateCode CHAR(2));


select OID,SUBSTR(Basename,1, INSTR(Basename,',',1)-1),
substr(basename,LENGTH(basename)-1,2)
from CENSUS_MSA;

result:

263904755925760 Anaheim-Santa Ana-Irvine CA

So how can I separate each city in one line with use multiple selection like : Anaheim CA Santa Ana CA Irvine CA

Well, splitting up the string by hyphens can be done; and in this case they are all in one state so you could then slap the state on the end of each substring from the name. But what would you do with "Fayetteville-Springdale-Rogers, AR-MO" - three city names but only two states? Which state goes with which city? — Dave Costa, May 01 '15 at 02:56
http://stackoverflow.com/questions/26407538/split-string-into-rows-oracle-sql gives an idea of how to do the strip splitting — Dave Costa, May 01 '15 at 03:01
my question is how do we separate each city for each state in a single line using substring and multiple select query? — Imank, May 01 '15 at 03:02

score 1 · Answer 1 · answered May 01 '15 at 05:40

According to your sample data and desired output, in fact you want to extract values between - in a string like Anaheim-Santa Ana-Irvine and then join them with a value like CA so the result as you mentioned should be:

Anaheim   CA
Santa Ana CA
Irvine    CA

Using regx_substr for such these scenarios is very useful, you can achive the result by cross joining the extracted values with a generated list(table) of ids with count of number of values in the complex string:

SQL> create table strTable(complexStr varchar(100),strId varchar(5));

Table created.

SQL> insert into strTable values('Anaheim-Santa Ana-Irvine','CA');

1 row created.

SQL> commit;

Commit complete.

SQL> select trim(regexp_substr(t.complexStr, '[^-]+', 1, ids.column_value)) city,
  2  t.strId state
  3  from strTable t
  4  cross join
  5  table (cast (multiset
  6  (select level from dual connect by level <= regexp_count(t.complexStr, '-')+1)
  7  AS sys.odciNumberList)) ids;

CITY        STATE
--------------------   
Anaheim     CA

Santa Ana   CA

Irvine      CA


SQL> drop table strTable;

Table dropped.

SQL>

multiple selection in single query oracle

1 Answers1