Other LTRIM SQL function
1. Usage of LTRIM to find products starting with some text
SQL Server Query 1
SELECT
ProductID,
ProductName
FROM
Products
WHERE
LTRIM(ProductName) LIKE 'Chef Anton%';
Create SQL query with SqlQueryBuilder 1
var (sql1, parameters1) = new SqlQueryBuilder()
.Select()
.Columns("ProductID", "ProductName")
.From("Products")
.Where(new Where(new LIKE(new LTRIM(new Column("ProductName")), "Chef Anton%")))
.Build();
Query build by SqlQueryBuilder 1
SELECT ProductID,
ProductName
FROM Products
WHERE LTRIM(ProductName) LIKE @pMAIN_2507200211126293610;
Parameters (If used)
Name |
Value |
@pMAIN_2507200211126293610 |
Chef Anton% |
Query Results 1:
|
ProductID |
ProductName |
1 |
4
|
Chef Anton's Cajun Seasoning
|
2 |
5
|
Chef Anton's Gumbo Mix
|
2. Usage of LTRIM to trim text
SQL Server Query 2
SELECT TOP 20
CustomerID,
CompanyName,
LTRIM(CompanyName) AS TrimmedCompanyName
FROM
Customers;
Create SQL query with SqlQueryBuilder 2
var (sql2, parameters2) = new SqlQueryBuilder()
.Select().Top(20)
.Columns("CustomerID", "CompanyName")
.Column(new LTRIM(new Column("CompanyName")), "TrimmedCompanyName")
.From("Customers")
.Build();
Query build by SqlQueryBuilder 2
SELECT TOP 20 CustomerID,
CompanyName,
LTRIM(CompanyName) AS TrimmedCompanyName
FROM Customers;
Parameters (If used)
Query Results 2:
|
CustomerID |
CompanyName |
TrimmedCompanyName |
1 |
ALFKI
|
Alfreds Futterkiste
|
Alfreds Futterkiste
|
2 |
ANATR
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo Emparedados y helados
|
3 |
ANTON
|
Antonio Moreno Taquería
|
Antonio Moreno Taquería
|
4 |
AROUT
|
Around the Horn
|
Around the Horn
|
5 |
BERGS
|
Berglunds snabbköp
|
Berglunds snabbköp
|
6 |
BLAUS
|
Blauer See Delikatessen
|
Blauer See Delikatessen
|
7 |
BLONP
|
Blondesddsl père et fils
|
Blondesddsl père et fils
|
8 |
BOLID
|
Bólido Comidas preparadas
|
Bólido Comidas preparadas
|
9 |
BONAP
|
Bon app'
|
Bon app'
|
10 |
BOTTM
|
Bottom-Dollar Markets
|
Bottom-Dollar Markets
|
11 |
BSBEV
|
B's Beverages
|
B's Beverages
|
12 |
CACTU
|
Cactus Comidas para llevar
|
Cactus Comidas para llevar
|
13 |
CENTC
|
Centro comercial Moctezuma
|
Centro comercial Moctezuma
|
14 |
CHOPS
|
Chop-suey Chinese
|
Chop-suey Chinese
|
15 |
COMMI
|
Comércio Mineiro
|
Comércio Mineiro
|
16 |
CONSH
|
Consolidated Holdings
|
Consolidated Holdings
|
17 |
WANDK
|
Die Wandernde Kuh
|
Die Wandernde Kuh
|
18 |
DRACD
|
Drachenblut Delikatessen
|
Drachenblut Delikatessen
|
19 |
DUMON
|
Du monde entier
|
Du monde entier
|
20 |
EASTC
|
Eastern Connection
|
Eastern Connection
|
3. Usage of LTRIM in WHERE clause
SQL Server Query 3
SELECT DISTINCT
s.SupplierID,
s.CompanyName,
e.Title AS RoleInAreaOfSupplier
FROM
Suppliers s
INNER JOIN
Employees e ON LTRIM(s.Country) = LTRIM(e.Country);
Create SQL query with SqlQueryBuilder 3
var (sql3, parameters3) = new SqlQueryBuilder()
.Select().Distinct()
.Columns("s.SupplierID", "s.CompanyName")
.Column("e.Title", "RoleInAreaOfSupplier")
.From("Suppliers", "s")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Employees", "e"))
.On(new LTRIM(new Column("s.Country")).Equale(new LTRIM(new Column("e.Country"))))
})
.Build();
Query build by SqlQueryBuilder 3
SELECT DISTINCT s.SupplierID,
s.CompanyName,
e.Title AS RoleInAreaOfSupplier
FROM Suppliers AS s
INNER JOIN
Employees AS e
ON LTRIM(s.Country) = LTRIM(e.Country);
Parameters (If used)
Query Results 3:
|
SupplierID |
CompanyName |
RoleInAreaOfSupplier |
1 |
1
|
Exotic Liquids
|
Sales Manager
|
2 |
1
|
Exotic Liquids
|
Sales Representative
|
3 |
2
|
New Orleans Cajun Delights
|
Inside Sales Coordinator
|
4 |
2
|
New Orleans Cajun Delights
|
Sales Representative
|
5 |
2
|
New Orleans Cajun Delights
|
Vice President, Sales
|
6 |
3
|
Grandma Kelly's Homestead
|
Inside Sales Coordinator
|
7 |
3
|
Grandma Kelly's Homestead
|
Sales Representative
|
8 |
3
|
Grandma Kelly's Homestead
|
Vice President, Sales
|
9 |
8
|
Specialty Biscuits, Ltd.
|
Sales Manager
|
10 |
8
|
Specialty Biscuits, Ltd.
|
Sales Representative
|
11 |
16
|
Bigfoot Breweries
|
Inside Sales Coordinator
|
12 |
16
|
Bigfoot Breweries
|
Sales Representative
|
13 |
16
|
Bigfoot Breweries
|
Vice President, Sales
|
14 |
19
|
New England Seafood Cannery
|
Inside Sales Coordinator
|
15 |
19
|
New England Seafood Cannery
|
Sales Representative
|
16 |
19
|
New England Seafood Cannery
|
Vice President, Sales
|
4. Usage of LTRIM to search company name starting with specfic text
SQL Server Query 4
SELECT
CustomerID,
CompanyName
FROM
Customers
WHERE
UPPER(LTRIM(CompanyName)) LIKE 'ALF%';
Create SQL query with SqlQueryBuilder 4
var (sql4, parameters4) = new SqlQueryBuilder()
.Select()
.Columns("CustomerID", "CompanyName")
.From("Customers")
.Where(new Where(new LIKE(new UPPER(new LTRIM(new Column("CompanyName"))), "ALF%")))
.Build();
Query build by SqlQueryBuilder 4
SELECT CustomerID,
CompanyName
FROM Customers
WHERE UPPER(LTRIM(CompanyName)) LIKE CONCAT(@pMAIN_2507200211126788070, @pMAIN_2507200211126788071);
Parameters (If used)
Name |
Value |
@pMAIN_2507200211126788070 |
ALF |
@pMAIN_2507200211126788071 |
% |
Query Results 4:
|
CustomerID |
CompanyName |
1 |
ALFKI
|
Alfreds Futterkiste
|
5. Usage of LTRIM to show first 5 letters of product
SQL Server Query 5
SELECT TOP 5
ProductID,
ProductName,
SUBSTRING(LTRIM(ProductName), 1, 5) AS FirstFiveTrimmed
FROM
Products
ORDER BY ProductID DESC
Create SQL query with SqlQueryBuilder 5
var (sql5, parameters5) = new SqlQueryBuilder()
.Select().Top(5)
.Columns("ProductID", "ProductName")
.Column(new SUBSTRING(new LTRIM(new Column("ProductName")), 1, 5), "FirstFiveTrimmed")
.From("Products")
.OrderBy(new OrderBy().SetColumnDescending("ProductID"))
.Build();
Query build by SqlQueryBuilder 5
SELECT TOP 5 ProductID,
ProductName,
SUBSTRING(LTRIM(ProductName), @pMAIN_2507200211126823110, @pMAIN_2507200211126823111) AS FirstFiveTrimmed
FROM Products
ORDER BY ProductID DESC;
Parameters (If used)
Name |
Value |
@pMAIN_2507200211126823110 |
1 |
@pMAIN_2507200211126823111 |
5 |
Query Results 5:
|
ProductID |
ProductName |
1 |
77
|
Original Frankfurter grüne Soße
|
2 |
76
|
Lakkalikööri
|
3 |
75
|
Rhönbräu Klosterbier
|
4 |
74
|
Longlife Tofu
|
5 |
73
|
Röd Kaviar
|