Other LEFT SQL function


1. Usage of LEFT to show First Letter of company

SQL Server Query 1

            
 SELECT CustomerID, CompanyName, CHAR(ASCII(LEFT(CompanyName, 1))) AS FirstLetterCharacter FROM Customers;   

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  
.Select()
.Columns("CustomerID", "CompanyName")
.Column(new CHAR(new ASCII(new LEFT(new Column("CompanyName"), 1))), "FirstLetterCharacter")
.From("Customers")
.Build();

Query build by SqlQueryBuilder 1

            
SELECT CustomerID,
       CompanyName,
       CHAR(ASCII(LEFT(CompanyName, @pMAIN_2507192048120241470))) AS FirstLetterCharacter
FROM Customers;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048120241470 1

Query Results 1:

  CustomerID CompanyName FirstLetterCharacter
1 ALFKI Alfreds Futterkiste A
2 ANATR Ana Trujillo Emparedados y helados A
3 ANTON Antonio Moreno Taquería A
4 AROUT Around the Horn A
5 BERGS Berglunds snabbköp B
6 BLAUS Blauer See Delikatessen B
7 BLONP Blondesddsl père et fils B
8 BOLID Bólido Comidas preparadas B
9 BONAP Bon app' B
10 BOTTM Bottom-Dollar Markets B
11 BSBEV B's Beverages B
12 CACTU Cactus Comidas para llevar C
13 CENTC Centro comercial Moctezuma C
14 CHOPS Chop-suey Chinese C
15 COMMI Comércio Mineiro C
16 CONSH Consolidated Holdings C
17 WANDK Die Wandernde Kuh D
18 DRACD Drachenblut Delikatessen D
19 DUMON Du monde entier D
20 EASTC Eastern Connection E
21 ERNSH Ernst Handel E
22 FAMIA Familia Arquibaldo F
23 FISSA FISSA Fabrica Inter. Salchichas S.A. F
24 FOLIG Folies gourmandes F
25 FOLKO Folk och fä HB F
26 FRANR France restauration F
27 FRANS Franchi S.p.A. F
28 FRANK Frankenversand F
29 FURIB Furia Bacalhau e Frutos do Mar F
30 GALED Galería del gastrónomo G
31 GODOS Godos Cocina Típica G
32 GOURL Gourmet Lanchonetes G
33 GREAL Great Lakes Food Market G
34 GROSR GROSELLA-Restaurante G
35 HANAR Hanari Carnes H
36 HILAA HILARION-Abastos H
37 HUNGC Hungry Coyote Import Store H
38 HUNGO Hungry Owl All-Night Grocers H
39 ISLAT Island Trading I
40 KOENE Königlich Essen K
41 LACOR La corne d'abondance L
42 LAMAI La maison d'Asie L
43 LAUGB Laughing Bacchus Wine Cellars L
44 LAZYK Lazy K Kountry Store L
45 LEHMS Lehmanns Marktstand L
46 LETSS Let's Stop N Shop L
47 LILAS LILA-Supermercado L
48 LINOD LINO-Delicateses L
49 LONEP Lonesome Pine Restaurant L
50 MAGAA Magazzini Alimentari Riuniti M
51 MAISD Maison Dewey M
52 MEREP Mère Paillarde M
53 MORGK Morgenstern Gesundkost M
54 NORTS North/South N
55 OCEAN Océano Atlántico Ltda. O
56 OLDWO Old World Delicatessen O
57 OTTIK Ottilies Käseladen O
58 PARIS Paris spécialités P
59 PERIC Pericles Comidas clásicas P
60 PICCO Piccolo und mehr P
61 PRINI Princesa Isabel Vinhos P
62 QUEDE Que Delícia Q
63 QUEEN Queen Cozinha Q
64 QUICK QUICK-Stop Q
65 RANCH Rancho grande R
66 RATTC Rattlesnake Canyon Grocery R
67 REGGC Reggiani Caseifici R
68 RICAR Ricardo Adocicados R
69 RICSU Richter Supermarkt R
70 ROMEY Romero y tomillo R
71 SANTG Santé Gourmet S
72 SAVEA Save-a-lot Markets S
73 SEVES Seven Seas Imports S
74 SIMOB Simons bistro S
75 SPECD Spécialités du monde S
76 SPLIR Split Rail Beer & Ale S
77 SUPRD Suprêmes délices S
78 THEBI The Big Cheese T
79 THECR The Cracker Box T
80 TOMSP Toms Spezialitäten T
81 TORTU Tortuga Restaurante T
82 TRADH Tradição Hipermercados T
83 TRAIH Trail's Head Gourmet Provisioners T
84 VAFFE Vaffeljernet V
85 VICTE Victuailles en stock V
86 VINET Vins et alcools Chevalier V
87 WARTH Wartian Herkku W
88 WELLI Wellington Importadora W
89 WHITC White Clover Markets W
90 WILMK Wilman Kala W
91 WOLZA Wolski Zajazd W


2. Usage of LEFT in WHERE clause

SQL Server Query 2

            
 SELECT CustomerID, CompanyName FROM Customers WHERE ASCII(LEFT(CompanyName, 1)) = 65;   

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  
.Select().Columns("CustomerID", "CompanyName")
.From("Customers")
.Where(new Where(new ASCII(new LEFT(new Column("CompanyName"), 1)).Equale(65)))
.Build();

Query build by SqlQueryBuilder 2

            
SELECT CustomerID,
       CompanyName
FROM Customers
WHERE ASCII(LEFT(CompanyName, @pMAIN_2507192048120272330)) = @pMAIN_2507192048120272331;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048120272330 1
@pMAIN_2507192048120272331 65

Query Results 2:

  CustomerID CompanyName
1 ALFKI Alfreds Futterkiste
2 ANATR Ana Trujillo Emparedados y helados
3 ANTON Antonio Moreno Taquería
4 AROUT Around the Horn


3. Usage of LEFT in WHERE clause

SQL Server Query 3

            
 SELECT ProductID, ProductName FROM Products WHERE ASCII(LEFT(ProductName, 1)) BETWEEN 65 AND 90;   

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  
.Select().Columns("ProductID", "ProductName")
.From("Products")
.Where(new Where(new BETWEEN(new Column(new ASCII(new Column(new LEFT(new Column("ProductName"), 1)))), 65, 90)))
.Build();

Query build by SqlQueryBuilder 3

            
SELECT ProductID,
       ProductName
FROM Products
WHERE ASCII(LEFT(ProductName, @pMAIN_2507192048120292460)) BETWEEN @pMAIN_2507192048120292461 AND @pMAIN_2507192048120292462;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048120292460 1
@pMAIN_2507192048120292461 65
@pMAIN_2507192048120292462 90

Query Results 3:

  ProductID ProductName
1 17 Alice Mutton
2 3 Aniseed Syrup
3 40 Boston Crab Meat
4 60 Camembert Pierrot
5 18 Carnarvon Tigers
6 1 Chai
7 2 Chang
8 39 Chartreuse verte
9 4 Chef Anton's Cajun Seasoning
10 5 Chef Anton's Gumbo Mix
11 48 Chocolade
12 38 Côte de Blaye
13 58 Escargots de Bourgogne
14 52 Filo Mix
15 71 Flotemysost
16 33 Geitost
17 15 Genen Shouyu
18 56 Gnocchi di nonna Alice
19 31 Gorgonzola Telino
20 6 Grandma's Boysenberry Spread
21 37 Gravad lax
22 24 Guaraná Fantástica
23 69 Gudbrandsdalsost
24 44 Gula Malacca
25 26 Gumbär Gummibärchen
26 22 Gustaf's Knäckebröd
27 10 Ikura
28 36 Inlagd Sill
29 43 Ipoh Coffee
30 41 Jack's New England Clam Chowder
31 13 Konbu
32 76 Lakkalikööri
33 67 Laughing Lumberjack Lager
34 74 Longlife Tofu
35 65 Louisiana Fiery Hot Pepper Sauce
36 66 Louisiana Hot Spiced Okra
37 51 Manjimup Dried Apples
38 32 Mascarpone Fabioli
39 49 Maxilaku
40 9 Mishi Kobe Niku
41 72 Mozzarella di Giovanni
42 30 Nord-Ost Matjeshering
43 8 Northwoods Cranberry Sauce
44 25 NuNuCa Nuß-Nougat-Creme
45 77 Original Frankfurter grüne Soße
46 70 Outback Lager
47 55 Pâté chinois
48 16 Pavlova
49 53 Perth Pasties
50 11 Queso Cabrales
51 12 Queso Manchego La Pastora
52 59 Raclette Courdavault
53 57 Ravioli Angelo
54 75 Rhönbräu Klosterbier
55 73 Röd Kaviar
56 45 Rogede sild
57 28 Rössle Sauerkraut
58 34 Sasquatch Ale
59 27 Schoggi Schokolade
60 68 Scottish Longbreads
61 42 Singaporean Hokkien Fried Mee
62 20 Sir Rodney's Marmalade
63 21 Sir Rodney's Scones
64 61 Sirop d'érable
65 46 Spegesild
66 35 Steeleye Stout
67 62 Tarte au sucre
68 19 Teatime Chocolate Biscuits
69 29 Thüringer Rostbratwurst
70 14 Tofu
71 54 Tourtière
72 23 Tunnbröd
73 7 Uncle Bob's Organic Dried Pears
74 50 Valkoinen suklaa
75 63 Vegie-spread
76 64 Wimmers gute Semmelknödel
77 47 Zaanse koeken


4. Usage of LEFT to show first character in OrderID

SQL Server Query 4

            
 SELECT TOP 15 OrderID, CHAR(LEFT(CAST(OrderID AS VARCHAR), 1) + 48) AS FirstDigitAsChar   
FROM Orders
WHERE OrderID BETWEEN 10248 AND 10250;

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  
.Select().Top(15).Column("OrderID", "OrderID")
.Column(new CHAR(new LEFT(new CAST(new Column("OrderID"), SqlDataType.VARCHAR), 1).AddArithmatic(new Arithmatic().Add(48))), "FirstDigitAsChar")
.From("Orders")
.Where(new Where(new BETWEEN(new Column("OrderID"), 10248, 10250)))
.Build();

Query build by SqlQueryBuilder 4

            
SELECT TOP 15 OrderID AS OrderID,
              CHAR(LEFT(CAST (OrderID AS VARCHAR), @pMAIN_2507192048120313870) + @pMAIN_2507192048120313871) AS FirstDigitAsChar
FROM Orders
WHERE OrderID BETWEEN @pMAIN_2507192048120313872 AND @pMAIN_2507192048120313873;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048120313870 1
@pMAIN_2507192048120313871 48
@pMAIN_2507192048120313872 10248
@pMAIN_2507192048120313873 10250

Query Results 4:

  OrderID FirstDigitAsChar
1 10248 1
2 10249 1
3 10250 1


5. Typical use of LEFT in WHERE clause

SQL Server Query 5

            
 SELECT CustomerID, CompanyName   
FROM Customers
WHERE CONCAT(LEFT(CompanyName, 1), RIGHT(CompanyName, 1)) = 'AS'

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  
.Select().Columns("CustomerID", "CompanyName")
.From("Customers")
.Where(new Where(new CONCAT(
new LEFT(new Column("CompanyName"), 1),
new RIGHT(new Column("CompanyName"), 1)
).Equale("AS")))
.Build();

Query build by SqlQueryBuilder 5

            
SELECT CustomerID,
       CompanyName
FROM Customers
WHERE CONCAT(LEFT(CompanyName, @pMAIN_2507192048120353610), RIGHT(CompanyName, @pMAIN_2507192048120353611)) = @pMAIN_2507192048120353612;


            
        

Parameters (If used)

Name Value
@pMAIN_2507192048120353610 1
@pMAIN_2507192048120353611 1
@pMAIN_2507192048120353612 AS

Query Results 5:

  CustomerID CompanyName
1 ANATR Ana Trujillo Emparedados y helados