CONCAT_WS()
CONCAT_WS() 함수는 구분 기호와 함께 두 개 이상의 표현식을 추가한다.
MySQL 4.0부터 작동한다.
형식
CONCAT_WS(separator, expression1, expression2, expression3,...)
매개변수
separator
:
각 식 사이에 추가할 구분 기호. 구분 기호가 NULL이면 이 함수는 NULL을 반환한다.
expression1, 2, 3…
:
추가할 표현식을 입력한다.
예제
여러 식을 함께 추가하고 그 사이에 “-” 구분 기호를 추가한다.
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;
기본 예시
| ConcatenatedString |
|---|
| SQL-Tutorial-is-fun! |
하나의 “주소” 열에 3개의 열을 추가하고 열 사이에 공백을 추가한다.
SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;
기본 예시
| CustomerName | Address |
|---|---|
| Alfreds Futterkiste | Obere Str. 57 12209 Berlin |
| Ana Trujillo Emparedados y helados | Avda. de la Constitución 2222 05021 México D.F. |
| Antonio Moreno Taquería | Mataderos 2312 05023 México D.F. |
| Around the Horn | 120 Hanover Sq. WA1 1DP London |
| Berglunds snabbköp | Berguvsvägen 8 S-958 22 Luleå |
| Blauer See Delikatessen | Forsterstr. 57 68306 Mannheim |
| Blondel père et fils | 24, place Kléber 67000 Strasbourg |
| Bólido Comidas preparadas | C/ Araquil, 67 28023 Madrid |
| Bon app’ | 12, rue des Bouchers 13008 Marseille |
| Bottom-Dollar Marketse | 23 Tsawassen Blvd. T2F 8M4 Tsawassen |
| B’s Beverages | Fauntleroy Circus EC2 5NT London |
| Cactus Comidas para llevar | Cerrito 333 1010 Buenos Aires |
| Centro comercial Moctezuma | Sierras de Granada 9993 05022 México D.F. |
| Chop-suey Chinese | Hauptstr. 29 3012 Bern |
| Comércio Mineiro | Av. dos Lusíadas, 23 05432-043 São Paulo |
| Consolidated Holdings | Berkeley Gardens 12 Brewery WX1 6LT London |
| Drachenblut Delikatessend | Walserweg 21 52066 Aachen |
| Du monde entier | 67, rue des Cinquante Otages 44000 Nantes |
| Eastern Connection | 35 King George WX3 6FW London |
| Ernst Handel | Kirchgasse 6 8010 Graz |
| Familia Arquibaldo | Rua Orós, 92 05442-030 São Paulo |
| FISSA Fabrica Inter. Salchichas S.A. | C/ Moralzarzal, 86 28034 Madrid |
| Folies gourmandes | 184, chaussée de Tournai 59000 Lille |
| Folk och fä HB | Åkergatan 24 S-844 67 Bräcke |
| Frankenversand | Berliner Platz 43 80805 München |
| France restauration | 54, rue Royale 44000 Nantes |
| Franchi S.p.A. | Via Monte Bianco 34 10100 Torino |
| Furia Bacalhau e Frutos do Mar | Jardim das rosas n. 32 1675 Lisboa |
| Galería del gastrónomo | Rambla de Cataluña, 23 08022 Barcelona |
| Godos Cocina Típica | C/ Romero, 33 41101 Sevilla |
| Gourmet Lanchonetes | Av. Brasil, 442 04876-786 Campinas |
| Great Lakes Food Market | 2732 Baker Blvd. 97403 Eugene |
| GROSELLA-Restaurante | 5ª Ave. Los Palos Grandes 1081 Caracas |
| Hanari Carnes | Rua do Paço, 67 05454-876 Rio de Janeiro |
| HILARIÓN-Abastos | Carrera 22 con Ave. Carlos Soublette #8-35 5022 San Cristóbal |
| Hungry Coyote Import Store | City Center Plaza 516 Main St. 97827 Elgin |
| Hungry Owl All-Night Grocers | 8 Johnstown Road Cork |
| Island Trading | Garden House Crowther Way PO31 7PJ Cowes |
| Königlich Essen | Maubelstr. 90 14776 Brandenburg |
| La corne d’abondance | 67, avenue de l’Europe 78000 Versailles |
| La maison d’Asie | 1 rue Alsace-Lorraine 31000 Toulouse |
| Laughing Bacchus Wine Cellars | 1900 Oak St. V3F 2K1 Vancouver |
| Lazy K Kountry Store | 12 Orchestra Terrace 99362 Walla Walla |
| Lehmanns Marktstand | Magazinweg 7 60528 Frankfurt a.M. |
| Let’s Stop N Shop | 87 Polk St. Suite 5 94117 San Francisco |
| LILA-Supermercado | Carrera 52 con Ave. Bolívar #65-98 Llano Largo 3508 Barquisimeto |
| LINO-Delicateses | Ave. 5 de Mayo Porlamar 4980 I. de Margarita |
| Lonesome Pine Restaurant | 89 Chiaroscuro Rd. 97219 Portland |
| Magazzini Alimentari Riuniti | Via Ludovico il Moro 22 24100 Bergamo |
| Maison Dewey | Rue Joseph-Bens 532 B-1180 Bruxelles |
| Mère Paillarde | 43 rue St. Laurent H1J 1C3 Montréal |
| Morgenstern Gesundkost | Heerstr. 22 04179 Leipzig |
| North/South | South House 300 Queensbridge SW7 1RZ London |
| Océano Atlántico Ltda. | Ing. Gustavo Moncada 8585 Piso 20-A 1010 Buenos Aires |
| Old World Delicatessen | 2743 Bering St. 99508 Anchorage |
| Ottilies Käseladen | Mehrheimerstr. 369 50739 Köln |
| Paris spécialités | 265, boulevard Charonne 75012 Paris |
| Pericles Comidas clásicas | Calle Dr. Jorge Cash 321 05033 México D.F. |
| Piccolo und mehr | Geislweg 14 5020 Salzburg |
| Princesa Isabel Vinhoss | Estrada da saúde n. 58 1756 Lisboa |
| Que Delícia | Rua da Panificadora, 12 02389-673 Rio de Janeiro |
| Queen Cozinha | Alameda dos Canàrios, 891 05487-020 São Paulo |
| QUICK-Stop | Taucherstraße 10 01307 Cunewalde |
| Rancho grande | Av. del Libertador 900 1010 Buenos Aires |
| Rattlesnake Canyon Grocery | 2817 Milton Dr. 87110 Albuquerque |
| Reggiani Caseifici | Strada Provinciale 124 42100 Reggio Emilia |
| Ricardo Adocicados | Av. Copacabana, 267 02389-890 Rio de Janeiro |
| Richter Supermarkt | Grenzacherweg 237 1203 Genève |
| Romero y tomillo | Gran Vía, 1 28001 Madrid |
| Santé Gourmet | Erling Skakkes gate 78 4110 Stavern |
| Save-a-lot Markets | 187 Suffolk Ln. 83720 Boise |
| Seven Seas Imports | 90 Wadhurst Rd. OX15 4NB London |
| Simons bistro | Vinbæltet 34 1734 København |
| Spécialités du monde | 25, rue Lauriston 75016 Paris |
| Split Rail Beer & Ale | P.O. Box 555 82520 Lander |
| Suprêmes délices | Boulevard Tirou, 255 B-6000 Charleroi |
| The Big Cheese | 89 Jefferson Way Suite 2 97201 Portland |
| The Cracker Box | 55 Grizzly Peak Rd. 59801 Butte |
| Toms Spezialitäten | Luisenstr. 48 44087 Münster |
| Tortuga Restaurante | Avda. Azteca 123 05033 México D.F. |
| Tradição Hipermercados | Av. Inês de Castro, 414 05634-030 São Paulo |
| Trail’s Head Gourmet Provisioners | 722 DaVinci Blvd. 98034 Kirkland |
| Vaffeljernet | Smagsløget 45 8200 Århus |
| Victuailles en stock | 2, rue du Commerce 69004 Lyon |
| Vins et alcools Chevalier | 59 rue de l’Abbaye 51100 Reims |
| Die Wandernde Kuh | Adenauerallee 900 70563 Stuttgart |
| Wartian Herkku | Torikatu 38 90110 Oulu |
| Wellington Importadora | Rua do Mercado, 12 08737-363 Resende |
| White Clover Markets | 305 – 14th Ave. S. Suite 3B 98128 Seattle |
| Wilman Kala | Keskuskatu 45 21240 Helsinki |
| Wolski | ul. Filtrowa 68 01-012 Walla |
참고
W3C School - MySQL CONCAT_WS() Function
W3C School - SQL Tutorial
공부 목적 블로그