Other CONCAT_WS SQL function
1. Usage of CONCAT_WS to show full name from employees table
SQL Server Query 1
SELECT EmployeeID, CONCAT_WS(' ', FirstName, LastName) AS FullName FROM Employees;
Create SQL query with SqlQueryBuilder 1
var (sql1, parameters1) = new SqlQueryBuilder()
.Select().Columns("EmployeeID", "EmployeeID")
.Column(new CONCAT_WS(" ", new Column("FirstName"), new Column("LastName")), "FullName")
.From("Employees")
.Build();
Query build by SqlQueryBuilder 1
SELECT EmployeeID, EmployeeID, CONCAT_WS(@pMAIN_2507192048123123930, FirstName, LastName) AS FullName FROM Employees;
Parameters (If used)
Name | Value |
---|---|
@pMAIN_2507192048123123930 |
Query Results 1:
EmployeeID | FullName | |
---|---|---|
1 | 1 | Nancy Davolio |
2 | 2 | Andrew Fuller |
3 | 3 | Janet Leverling |
4 | 4 | Margaret Peacock |
5 | 5 | Steven Buchanan |
6 | 6 | Michael Suyama |
7 | 7 | Robert King |
8 | 8 | Laura Callahan |
9 | 9 | Anne Dodsworth |
2. Usage of CONCAT_WS to create customer identifier
SQL Server Query 2
SELECT CONCAT_WS(' - ', CustomerID, CompanyName) AS CustomerIdentifier FROM Customers;
Create SQL query with SqlQueryBuilder 2
var (sql2, parameters2) = new SqlQueryBuilder()
.Select()
.Column(new CONCAT_WS(" ", new Column("CustomerID"), new Column("CompanyName")), "CustomerIdentifier")
.From("Customers")
.Build();
Query build by SqlQueryBuilder 2
SELECT CONCAT_WS(@pMAIN_2507192048123161610, CustomerID, CompanyName) AS CustomerIdentifier FROM Customers;
Parameters (If used)
Name | Value |
---|---|
@pMAIN_2507192048123161610 |
Query Results 2:
CustomerIdentifier | |
---|---|
1 | ALFKI Alfreds Futterkiste |
2 | ANATR Ana Trujillo Emparedados y helados |
3 | ANTON Antonio Moreno Taquería |
4 | AROUT Around the Horn |
5 | BERGS Berglunds snabbköp |
6 | BLAUS Blauer See Delikatessen |
7 | BLONP Blondesddsl père et fils |
8 | BOLID Bólido Comidas preparadas |
9 | BONAP Bon app' |
10 | BOTTM Bottom-Dollar Markets |
11 | BSBEV B's Beverages |
12 | CACTU Cactus Comidas para llevar |
13 | CENTC Centro comercial Moctezuma |
14 | CHOPS Chop-suey Chinese |
15 | COMMI Comércio Mineiro |
16 | CONSH Consolidated Holdings |
17 | WANDK Die Wandernde Kuh |
18 | DRACD Drachenblut Delikatessen |
19 | DUMON Du monde entier |
20 | EASTC Eastern Connection |
21 | ERNSH Ernst Handel |
22 | FAMIA Familia Arquibaldo |
23 | FISSA FISSA Fabrica Inter. Salchichas S.A. |
24 | FOLIG Folies gourmandes |
25 | FOLKO Folk och fä HB |
26 | FRANR France restauration |
27 | FRANS Franchi S.p.A. |
28 | FRANK Frankenversand |
29 | FURIB Furia Bacalhau e Frutos do Mar |
30 | GALED Galería del gastrónomo |
31 | GODOS Godos Cocina Típica |
32 | GOURL Gourmet Lanchonetes |
33 | GREAL Great Lakes Food Market |
34 | GROSR GROSELLA-Restaurante |
35 | HANAR Hanari Carnes |
36 | HILAA HILARION-Abastos |
37 | HUNGC Hungry Coyote Import Store |
38 | HUNGO Hungry Owl All-Night Grocers |
39 | ISLAT Island Trading |
40 | KOENE Königlich Essen |
41 | LACOR La corne d'abondance |
42 | LAMAI La maison d'Asie |
43 | LAUGB Laughing Bacchus Wine Cellars |
44 | LAZYK Lazy K Kountry Store |
45 | LEHMS Lehmanns Marktstand |
46 | LETSS Let's Stop N Shop |
47 | LILAS LILA-Supermercado |
48 | LINOD LINO-Delicateses |
49 | LONEP Lonesome Pine Restaurant |
50 | MAGAA Magazzini Alimentari Riuniti |
51 | MAISD Maison Dewey |
52 | MEREP Mère Paillarde |
53 | MORGK Morgenstern Gesundkost |
54 | NORTS North/South |
55 | OCEAN Océano Atlántico Ltda. |
56 | OLDWO Old World Delicatessen |
57 | OTTIK Ottilies Käseladen |
58 | PARIS Paris spécialités |
59 | PERIC Pericles Comidas clásicas |
60 | PICCO Piccolo und mehr |
61 | PRINI Princesa Isabel Vinhos |
62 | QUEDE Que Delícia |
63 | QUEEN Queen Cozinha |
64 | QUICK QUICK-Stop |
65 | RANCH Rancho grande |
66 | RATTC Rattlesnake Canyon Grocery |
67 | REGGC Reggiani Caseifici |
68 | RICAR Ricardo Adocicados |
69 | RICSU Richter Supermarkt |
70 | ROMEY Romero y tomillo |
71 | SANTG Santé Gourmet |
72 | SAVEA Save-a-lot Markets |
73 | SEVES Seven Seas Imports |
74 | SIMOB Simons bistro |
75 | SPECD Spécialités du monde |
76 | SPLIR Split Rail Beer & Ale |
77 | SUPRD Suprêmes délices |
78 | THEBI The Big Cheese |
79 | THECR The Cracker Box |
80 | TOMSP Toms Spezialitäten |
81 | TORTU Tortuga Restaurante |
82 | TRADH Tradição Hipermercados |
83 | TRAIH Trail's Head Gourmet Provisioners |
84 | VAFFE Vaffeljernet |
85 | VICTE Victuailles en stock |
86 | VINET Vins et alcools Chevalier |
87 | WARTH Wartian Herkku |
88 | WELLI Wellington Importadora |
89 | WHITC White Clover Markets |
90 | WILMK Wilman Kala |
91 | WOLZA Wolski Zajazd |
3. Usage of CONCAT_WS to format customer address
SQL Server Query 3
SELECT CustomerID, CONCAT_WS(', ', Address, City, Region, PostalCode, Country) AS CustomerAddress
FROM Customers;
Create SQL query with SqlQueryBuilder 3
var (sql3, parameters3) = new SqlQueryBuilder()
.Select().Column("CustomerID", "CustomerID")
.Column(new CONCAT_WS(", ", new Column("Address"), new Column("City"), new Column("Region"),
new Column("PostalCode"), new Column("Country")), "CustomerAddress")
.From("Customers")
.Build();
Query build by SqlQueryBuilder 3
SELECT CustomerID AS CustomerID, CONCAT_WS(@pMAIN_2507192048123185230, Address, City, Region, PostalCode, Country) AS CustomerAddress FROM Customers;
Parameters (If used)
Name | Value |
---|---|
@pMAIN_2507192048123185230 | , |
Query Results 3:
CustomerID | CustomerAddress | |
---|---|---|
1 | ALFKI | Obere Str. 57, Berlin, 12209, Germany |
2 | ANATR | Avda. de la Constitución 2222, México D.F., 05021, Mexico |
3 | ANTON | Mataderos 2312, México D.F., 05023, Mexico |
4 | AROUT | 120 Hanover Sq., London, WA1 1DP, UK |
5 | BERGS | Berguvsvägen 8, Luleå, S-958 22, Sweden |
6 | BLAUS | Forsterstr. 57, Mannheim, 68306, Germany |
7 | BLONP | 24, place Kléber, Strasbourg, 67000, France |
8 | BOLID | C/ Araquil, 67, Madrid, 28023, Spain |
9 | BONAP | 12, rue des Bouchers, Marseille, 13008, France |
10 | BOTTM | 23 Tsawassen Blvd., Tsawassen, BC, T2F 8M4, Canada |
11 | BSBEV | Fauntleroy Circus, London, EC2 5NT, UK |
12 | CACTU | Cerrito 333, Buenos Aires, 1010, Argentina |
13 | CENTC | Sierras de Granada 9993, México D.F., 05022, Mexico |
14 | CHOPS | Hauptstr. 29, Bern, 3012, Switzerland |
15 | COMMI | Av. dos Lusíadas, 23, Sao Paulo, SP, 05432-043, Brazil |
16 | CONSH | Berkeley Gardens 12 Brewery, London, WX1 6LT, UK |
17 | DRACD | Walserweg 21, Aachen, 52066, Germany |
18 | DUMON | 67, rue des Cinquante Otages, Nantes, 44000, France |
19 | EASTC | 35 King George, London, WX3 6FW, UK |
20 | ERNSH | Kirchgasse 6, Graz, 8010, Austria |
21 | FAMIA | Rua Orós, 92, Sao Paulo, SP, 05442-030, Brazil |
22 | FISSA | C/ Moralzarzal, 86, Madrid, 28034, Spain |
23 | FOLIG | 184, chaussée de Tournai, Lille, 59000, France |
24 | FOLKO | Åkergatan 24, Bräcke, S-844 67, Sweden |
25 | FRANK | Berliner Platz 43, München, 80805, Germany |
26 | FRANR | 54, rue Royale, Nantes, 44000, France |
27 | FRANS | Via Monte Bianco 34, Torino, 10100, Italy |
28 | FURIB | Jardim das rosas n. 32, Lisboa, 1675, Portugal |
29 | GALED | Rambla de Cataluña, 23, Barcelona, 08022, Spain |
30 | GODOS | C/ Romero, 33, Sevilla, 41101, Spain |
31 | GOURL | Av. Brasil, 442, Campinas, SP, 04876-786, Brazil |
32 | GREAL | 2732 Baker Blvd., Eugene, OR, 97403, USA |
33 | GROSR | 5ª Ave. Los Palos Grandes, Caracas, DF, 1081, Venezuela |
34 | HANAR | Rua do Paço, 67, Rio de Janeiro, RJ, 05454-876, Brazil |
35 | HILAA | Carrera 22 con Ave. Carlos Soublette #8-35, San Cristóbal, Táchira, 5022, Venezuela |
36 | HUNGC | City Center Plaza 516 Main St., Elgin, OR, 97827, USA |
37 | HUNGO | 8 Johnstown Road, Cork, Co. Cork, Ireland |
38 | ISLAT | Garden House Crowther Way, Cowes, Isle of Wight, PO31 7PJ, UK |
39 | KOENE | Maubelstr. 90, Brandenburg, 14776, Germany |
40 | LACOR | 67, avenue de l'Europe, Versailles, 78000, France |
41 | LAMAI | 1 rue Alsace-Lorraine, Toulouse, 31000, France |
42 | LAUGB | 1900 Oak St., Vancouver, BC, V3F 2K1, Canada |
43 | LAZYK | 12 Orchestra Terrace, Walla Walla, WA, 99362, USA |
44 | LEHMS | Magazinweg 7, Frankfurt a.M., 60528, Germany |
45 | LETSS | 87 Polk St. Suite 5, San Francisco, CA, 94117, USA |
46 | LILAS | Carrera 52 con Ave. Bolívar #65-98 Llano Largo, Barquisimeto, Lara, 3508, Venezuela |
47 | LINOD | Ave. 5 de Mayo Porlamar, I. de Margarita, Nueva Esparta, 4980, Venezuela |
48 | LONEP | 89 Chiaroscuro Rd., Portland, OR, 97219, USA |
49 | MAGAA | Via Ludovico il Moro 22, Bergamo, 24100, Italy |
50 | MAISD | Rue Joseph-Bens 532, Bruxelles, B-1180, Belgium |
51 | MEREP | 43 rue St. Laurent, Montréal, Québec, H1J 1C3, Canada |
52 | MORGK | Heerstr. 22, Leipzig, 04179, Germany |
53 | NORTS | South House 300 Queensbridge, London, SW7 1RZ, UK |
54 | OCEAN | Ing. Gustavo Moncada 8585 Piso 20-A, Buenos Aires, 1010, Argentina |
55 | OLDWO | 2743 Bering St., Anchorage, AK, 99508, USA |
56 | OTTIK | Mehrheimerstr. 369, Köln, 50739, Germany |
57 | PARIS | 265, boulevard Charonne, Paris, 75012, France |
58 | PERIC | Calle Dr. Jorge Cash 321, México D.F., 05033, Mexico |
59 | PICCO | Geislweg 14, Salzburg, 5020, Austria |
60 | PRINI | Estrada da saúde n. 58, Lisboa, 1756, Portugal |
61 | QUEDE | Rua da Panificadora, 12, Rio de Janeiro, RJ, 02389-673, Brazil |
62 | QUEEN | Alameda dos Canàrios, 891, Sao Paulo, SP, 05487-020, Brazil |
63 | QUICK | Taucherstraße 10, Cunewalde, 01307, Germany |
64 | RANCH | Av. del Libertador 900, Buenos Aires, 1010, Argentina |
65 | RATTC | 2817 Milton Dr., Albuquerque, NM, 87110, USA |
66 | REGGC | Strada Provinciale 124, Reggio Emilia, 42100, Italy |
67 | RICAR | Av. Copacabana, 267, Rio de Janeiro, RJ, 02389-890, Brazil |
68 | RICSU | Grenzacherweg 237, Genève, 1203, Switzerland |
69 | ROMEY | Gran Vía, 1, Madrid, 28001, Spain |
70 | SANTG | Erling Skakkes gate 78, Stavern, 4110, Norway |
71 | SAVEA | 187 Suffolk Ln., Boise, ID, 83720, USA |
72 | SEVES | 90 Wadhurst Rd., London, OX15 4NB, UK |
73 | SIMOB | Vinbæltet 34, Kobenhavn, 1734, Denmark |
74 | SPECD | 25, rue Lauriston, Paris, 75016, France |
75 | SPLIR | P.O. Box 555, Lander, WY, 82520, USA |
76 | SUPRD | Boulevard Tirou, 255, Charleroi, B-6000, Belgium |
77 | THEBI | 89 Jefferson Way Suite 2, Portland, OR, 97201, USA |
78 | THECR | 55 Grizzly Peak Rd., Butte, MT, 59801, USA |
79 | TOMSP | Luisenstr. 48, Münster, 44087, Germany |
80 | TORTU | Avda. Azteca 123, México D.F., 05033, Mexico |
81 | TRADH | Av. Inês de Castro, 414, Sao Paulo, SP, 05634-030, Brazil |
82 | TRAIH | 722 DaVinci Blvd., Kirkland, WA, 98034, USA |
83 | VAFFE | Smagsloget 45, Århus, 8200, Denmark |
84 | VICTE | 2, rue du Commerce, Lyon, 69004, France |
85 | VINET | 59 rue de l'Abbaye, Reims, 51100, France |
86 | WANDK | Adenauerallee 900, Stuttgart, 70563, Germany |
87 | WARTH | Torikatu 38, Oulu, 90110, Finland |
88 | WELLI | Rua do Mercado, 12, Resende, SP, 08737-363, Brazil |
89 | WHITC | 305 - 14th Ave. S. Suite 3B, Seattle, WA, 98128, USA |
90 | WILMK | Keskuskatu 45, Helsinki, 21240, Finland |
91 | WOLZA | ul. Filtrowa 68, Warszawa, 01-012, Poland |
4. Usage of CONCAT_WS to format product details
SQL Server Query 4
SELECT TOP 15 ProductName, CONCAT_WS(CHAR(9), ProductName, UnitPrice, UnitsInStock) AS ProductDetailsWithTabs
FROM Products;
Create SQL query with SqlQueryBuilder 4
var (sql4, parameters4) = new SqlQueryBuilder()
.Select().Top(15).Column("ProductName", "ProductName")
.Column(new CONCAT_WS(new CHAR(9), new Column("ProductName"), new Column("UnitPrice"),
new Column("UnitsInStock")), "ProductDetailsWithTabs")
.From("Products")
.Build();
Query build by SqlQueryBuilder 4
SELECT TOP 15 ProductName AS ProductName, CONCAT_WS(CHAR(@pMAIN_2507192048123215060), ProductName, UnitPrice, UnitsInStock) AS ProductDetailsWithTabs FROM Products;
Parameters (If used)
Name | Value |
---|---|
@pMAIN_2507192048123215060 | 9 |
Query Results 4:
ProductName | ProductDetailsWithTabs | |
---|---|---|
1 | Chai | Chai 18.00 39 |
2 | Chang | Chang 19.00 17 |
3 | Aniseed Syrup | Aniseed Syrup 10.00 13 |
4 | Chef Anton's Cajun Seasoning | Chef Anton's Cajun Seasoning 22.00 53 |
5 | Chef Anton's Gumbo Mix | Chef Anton's Gumbo Mix 21.35 0 |
6 | Grandma's Boysenberry Spread | Grandma's Boysenberry Spread 25.00 120 |
7 | Uncle Bob's Organic Dried Pears | Uncle Bob's Organic Dried Pears 30.00 15 |
8 | Northwoods Cranberry Sauce | Northwoods Cranberry Sauce 40.00 6 |
9 | Mishi Kobe Niku | Mishi Kobe Niku 97.00 29 |
10 | Ikura | Ikura 31.00 31 |
11 | Queso Cabrales | Queso Cabrales 21.00 22 |
12 | Queso Manchego La Pastora | Queso Manchego La Pastora 38.00 86 |
13 | Konbu | Konbu 6.00 24 |
14 | Tofu | Tofu 23.25 35 |
15 | Genen Shouyu | Genen Shouyu 15.50 39 |
5. Usage of CONCAT_WS to format order details
SQL Server Query 5
SELECT TOP 25 OrderID, ProductID,
CONCAT_WS(' | ', 'Product ID: ' + CAST(ProductID AS VARCHAR), 'Quantity: ' + CAST(Quantity AS VARCHAR), 'Unit Price: $' +
CAST(UnitPrice AS VARCHAR)) AS OrderDetailInfo
FROM [Order Details];
Create SQL query with SqlQueryBuilder 5
var (sql5, parameters5) = new SqlQueryBuilder()
.Select().Top(25).Columns("OrderID", "ProductID")
.Column(new CONCAT_WS(" | ", new CONCAT("Product ID: ", new CAST(new Column("ProductID"), SqlDataType.VARCHAR)),
new CONCAT("Quantity: ", new CAST(new Column("Quantity"), SqlDataType.VARCHAR)),
new CONCAT("Unit Price: $", new CAST(new Column("UnitPrice"), SqlDataType.VARCHAR))), "OrderDetailInfo")
.From("[Order Details]")
.Build();
Query build by SqlQueryBuilder 5
SELECT TOP 25 OrderID, ProductID, CONCAT_WS(@pMAIN_2507192048123243600, CONCAT(@pMAIN_2507192048123243601, CAST (ProductID AS VARCHAR)), CONCAT(@pMAIN_2507192048123243602, CAST (Quantity AS VARCHAR)), CONCAT(@pMAIN_2507192048123243603, CAST (UnitPrice AS VARCHAR))) AS OrderDetailInfo FROM [Order Details];
Parameters (If used)
Name | Value |
---|---|
@pMAIN_2507192048123243600 | | |
@pMAIN_2507192048123243601 | Product ID: |
@pMAIN_2507192048123243602 | Quantity: |
@pMAIN_2507192048123243603 | Unit Price: $ |
Query Results 5:
OrderID | ProductID | OrderDetailInfo | |
---|---|---|---|
1 | 10248 | 11 | Product ID: 11 | Quantity: 12 | Unit Price: $14.00 |
2 | 10248 | 42 | Product ID: 42 | Quantity: 10 | Unit Price: $9.80 |
3 | 10248 | 72 | Product ID: 72 | Quantity: 5 | Unit Price: $34.80 |
4 | 10249 | 14 | Product ID: 14 | Quantity: 9 | Unit Price: $18.60 |
5 | 10249 | 51 | Product ID: 51 | Quantity: 40 | Unit Price: $42.40 |
6 | 10250 | 41 | Product ID: 41 | Quantity: 10 | Unit Price: $7.70 |
7 | 10250 | 51 | Product ID: 51 | Quantity: 35 | Unit Price: $42.40 |
8 | 10250 | 65 | Product ID: 65 | Quantity: 15 | Unit Price: $16.80 |
9 | 10251 | 22 | Product ID: 22 | Quantity: 6 | Unit Price: $16.80 |
10 | 10251 | 57 | Product ID: 57 | Quantity: 15 | Unit Price: $15.60 |
11 | 10251 | 65 | Product ID: 65 | Quantity: 20 | Unit Price: $16.80 |
12 | 10252 | 20 | Product ID: 20 | Quantity: 40 | Unit Price: $64.80 |
13 | 10252 | 33 | Product ID: 33 | Quantity: 25 | Unit Price: $2.00 |
14 | 10252 | 60 | Product ID: 60 | Quantity: 40 | Unit Price: $27.20 |
15 | 10253 | 31 | Product ID: 31 | Quantity: 20 | Unit Price: $10.00 |
16 | 10253 | 39 | Product ID: 39 | Quantity: 42 | Unit Price: $14.40 |
17 | 10253 | 49 | Product ID: 49 | Quantity: 40 | Unit Price: $16.00 |
18 | 10254 | 24 | Product ID: 24 | Quantity: 15 | Unit Price: $3.60 |
19 | 10254 | 55 | Product ID: 55 | Quantity: 21 | Unit Price: $19.20 |
20 | 10254 | 74 | Product ID: 74 | Quantity: 21 | Unit Price: $8.00 |
21 | 10255 | 2 | Product ID: 2 | Quantity: 20 | Unit Price: $15.20 |
22 | 10255 | 16 | Product ID: 16 | Quantity: 35 | Unit Price: $13.90 |
23 | 10255 | 36 | Product ID: 36 | Quantity: 25 | Unit Price: $15.20 |
24 | 10255 | 59 | Product ID: 59 | Quantity: 30 | Unit Price: $44.00 |
25 | 10256 | 53 | Product ID: 53 | Quantity: 15 | Unit Price: $26.20 |