如何根据SQL Oracle中的相应字母列出表中的名称

如何获得所有在客户名称中带有连续字母“ co”的客户的列表。列表中必须包含客户的ID,姓名以及姓名的升序排列。

我提供了一个表格和内容来为客户创建,并希望列出当时带有连续字母“ co”的名称

CREATE TABLE customers
      (
        customer_id NUMBER 
                    GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                    PRIMARY KEY,
        name         VARCHAR2( 255 ) NOT NULL,
        address      VARCHAR2( 255 )         ,
        website      VARCHAR2( 255 )         ,
        credit_limit NUMBER( 8, 2 )
      );

    REM INSERTING into CUSTOMERS
    SET DEFINE OFF;
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (177,'United Continental Holdings','2904 S Salina St, Syracuse, NY',5000,'http://www.unitedcontinentalholdings.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (180,'INTL FCStone','5344 Haverford Ave, Philadelphia, PA',5000,'http://www.intlfcstone.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (184,'Publix Super Markets','1795 Wu Meng, Muang Chonburi, ',1200,'http://www.publix.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (187,'ConocoPhillips','Walpurgisstr 69, Munich, ',2400,'http://www.conocophillips.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (190,'3M','Via Frenzy 6903, Roma, ',1200,'http://www.3m.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (192,'Exelon','Via Luminosa 162, Firenze, ',500,'http://www.exeloncorp.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (208,'Tesoro','Via Notoriosa 1942, Firenze, ',500,'http://www.tsocorp.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (207,'Northwestern Mutual','1831 No Wong, Peking, ',3600,'http://www.northwesternmutual.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (200,'Enterprise Products Partners','Via Notoriosa 1949, Firenze, ',2400,'http://www.enterpriseproducts.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (204,'Rite Aid','Piazza Cacchiatore 23, San Giminiano, ',3600,'http://www.riteaid.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (212,'Qualcomm','Piazza Svizzera, Milano, ',500,'http://www.qualcomm.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (216,'EMC','Via Delle Grazie 11, San Giminiano, ',700,'http://www.emc.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (220,'Time Warner Cable','1597 Legend St, Mysore, Kar',3700,'http://www.twc.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (223,'Northrop Grumman','1606 Sangam Blvd, New Delhi, ',5000,'http://www.northropgrumman.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (39,'Lear','2115 N Towne Ln Ne, Cedar Rapids, IA',500,'http://www.lear.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (43,'Facebook','5112 Sw 9Th St, Des Moines, IA',500,'http://www.facebook.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (46,'Supervalu','8989 N Port Washington Rd, Milwaukee, WI',500,'http://www.supervalu.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (49,'NextEra Energy','4715 Sprecher Rd, Madison, WI',600,'http://www.nexteraenergy.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (52,'PG&E Corp.','8600 W National Ave, Milwaukee, WI',700,'http://www.pge.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (55,'Goodyear Tire & Rubber','600 N Broadway Fl 1, Milwaukee, WI',700,'http://www.goodyear.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (59,'Micron Technology','411 E Wisconsin Ave # 2550, Milwaukee, WI',700,'http://www.micron.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (62,'ConAgra Foods','122 E Dayton St, Madison, WI',900,'http://www.conagrafoods.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (66,'Bank of New York Mellon Corp.','666 22Nd Ave Ne, Minneapolis, MN',1200,'http://www.bnymellon.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (71,'Genuine Parts','200 1St St Sw, Rochester, MN',1200,'http://www.genpt.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (75,'Omnicom Group','2134 W Genesee St, Syracuse, NY',1200,'http://www.omnicomgroup.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (79,'Monsanto','137 Lark St, Albany, NY',1200,'http://www.monsanto.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (83,'National Oilwell Varco','1 Palisade Ave Fl 2, Yonkers, NY',1200,'http://www.nov.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (86,'Marriott International','Po Box 2152, Buffalo, NY',1200,'http://www.marriott.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (228,'Kinder Morgan','1614 Gitanjali Rd, Calcutta, ',5000,'http://www.kindermorgan.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (231,'Molina Healthcare','1617 Crackers St, Bangalore - India, Kar',300,'http://www.molinahealthcare.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (235,'Lincoln National','1622 Roja St, Chennai, Tam',5000,'http://www.lfg.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (238,'C.H. Robinson Worldwide','1628 Pataudi St, New Delhi, ',1900,'http://www.chrobinson.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (242,'Synnex','1647 Suspense St, Cochin, Ker',500,'http://www.synnex.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (245,'HollyFrontier','1656 Veterans Rd, Chennai, Tam',2500,'http://www.hollyfrontier.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (249,'PBF Energy','Piazza Quattre Stagioni 4, Roma, ',3700,'http://www.pbfenergy.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (255,'Waste Management','Via Di Firenze 231, Roma, ',2400,'http://www.wm.com');
    Insert into CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (259,'Parker-Hannifin','Canale Grande 2, Roma, ',700,'http://www.parker.com');
评论
  • baba
    baba 回复

    INSTR might be one option; I presumed that you want any "co" string, regardless its letter case (that's why I used lower function). If you don't need it, remove it.

    SQL> select customer_id, name
      2  from customers
      3  where instr(lower(name), 'co') > 0
      4  order by name;
    
    CUSTOMER_ID NAME
    ----------- --------------------------------------------------
             66 Bank of New York Mellon Corp.
             62 ConAgra Foods
            187 ConocoPhillips
            235 Lincoln National
             83 National Oilwell Varco
             75 Omnicom Group
             52 PG&E Corp.
            212 Qualcomm
            177 United Continental Holdings
    
    9 rows selected.
    
    SQL>