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
|