LOWER SQL function

1. Usage of LOWER to make some shipper lower.

SQL Server Query 1

            
SELECT   

    c.CustomerID,  

    c.CompanyName,  

    LOWER(c.CompanyName) AS LowerCaseCompanyName,  

    o.OrderID,  

    CASE  

        WHEN LOWER(s.CompanyName) LIKE 'united package%' THEN 'UPS'  

        WHEN LOWER(s.CompanyName) LIKE '%federal express%' THEN 'FedEx'  

        ELSE LOWER(s.CompanyName)  

    END AS LowerCaseShipperName  

FROM  

    Customers c  

LEFT JOIN  

    Orders o ON c.CustomerID = o.CustomerID  

LEFT JOIN  

    Shippers s ON o.ShipVia = s.ShipperID  

WHERE LEN(c.CustomerID) > 4 AND o.OrderID BETWEEN 10250 AND 10260 OR o.OrderID BETWEEN 11060 AND 11070;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("c.CustomerID", "c.CompanyName")  

    .Column(new LOWER(new Column("c.CompanyName")), "LowerCaseCompanyName")  

    .Columns("o.OrderID")  

    .Column(new CASE()  

        .When(new LIKE(new LOWER(new Column("s.CompanyName")), "united package%"))  

            .Then("UPS")  

        .When(new LIKE(new LOWER(new Column("s.CompanyName")), "%federal express%"))  

            .Then("FedEx")  

        .Else(new LOWER(new Column("s.CompanyName")))  

    , "LowerCaseShipperName")  

    .From("Customers", "c")  

    .Join(new List<IJoin>()  

    {  

        new LEFTJOIN().TableName(new Table("Orders", "o"))  

            .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID"))),  

        new LEFTJOIN().TableName(new Table("Shippers", "s"))  

            .On(new Column("o.ShipVia").Equale(new Column("s.ShipperID")))  

    })  

    .Where(new Where(new LEN(new Column("c.CustomerID")).GreaterThan(4))  

        .AND(new BETWEEN(new Column("o.OrderID"), 10250, 10260))  

        .OR(new BETWEEN(new Column("o.OrderID"), 11060, 11070))  

    )  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CustomerID,
       c.CompanyName,
       LOWER(c.CompanyName) AS LowerCaseCompanyName,
       o.OrderID,
       CASE WHEN LOWER(s.CompanyName) LIKE @pMAIN_2507192021012798760 THEN @pMAIN_2507192021012798761 WHEN LOWER(s.CompanyName) LIKE @pMAIN_2507192021012798762 THEN @pMAIN_2507192021012798763 ELSE LOWER(s.CompanyName) END AS LowerCaseShipperName
FROM Customers AS c
     LEFT OUTER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
     LEFT OUTER JOIN
     Shippers AS s
     ON o.ShipVia = s.ShipperID
WHERE LEN(c.CustomerID) > @pMAIN_2507192021012798764
      AND o.OrderID BETWEEN @pMAIN_2507192021012798765 AND @pMAIN_2507192021012798766
      OR o.OrderID BETWEEN @pMAIN_2507192021012798767 AND @pMAIN_2507192021012798768;


            
        

Parameters (If used)

Query Results 1: