时间戳

首页 > 解决方案 > 我如何根据sql oracle中的后续字母列出表中的名称

sql - 我如何根据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');

标签： sqloracle

解决方案

INSTR可能是一种选择；我假设你想要任何“co”字符串，不管它的字母大小写（这就是我使用lower函数的原因）。如果您不需要它，请将其删除。

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>

推荐阅读