PATINDEX SQL function

1. Usage of CONCAT to format names of employee.

SQL Server Query 1

            
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName   

FROM Employees;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Columns("EmployeeID", "EmployeeID")  

    .Column(new CONCAT(new Column("FirstName"), " ", new Column("LastName")), "FullName")  

    .From("Employees")  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT EmployeeID,
       EmployeeID,
       CONCAT(FirstName, @pMAIN_2507192016472254000, LastName) AS FullName
FROM Employees;


            
        

Parameters (If used)

Query Results 1:

2. Usage of CONCAT to create customer identifier.

SQL Server Query 2

            
SELECT CONCAT(CustomerID, ' - ', CompanyName) AS CustomerIdentifier   

FROM Customers;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CONCAT(new Column("CustomerID"), " - ", new Column("CompanyName")), "CustomerIdentifier")  

    .From("Customers")  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CONCAT(CustomerID, @pMAIN_2507192016472279530, CompanyName) AS CustomerIdentifier
FROM Customers;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CONCAT to create custom product description.

SQL Server Query 3

            
SELECT ProductName, CONCAT('Price: $', UnitPrice) AS ProductDescription   

FROM Products;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select().Column("ProductName", "ProductName")  

    .Column(new CONCAT("Price: $", new Column("UnitPrice")), "ProductDescription")  

    .From("Products")  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT ProductName AS ProductName,
       CONCAT(@pMAIN_2507192016472343430, UnitPrice) AS ProductDescription
FROM Products;


            
        

Parameters (If used)

Query Results 3:

4. Usage of CONCAT to create custom order summary.

SQL Server Query 4

            
SELECT OrderID,   

	CONCAT('Order #', OrderID, ' placed on ', OrderDate, ' by customer ', CustomerID) AS OrderSummary   

FROM Orders;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select().Column("OrderID", "OrderID")  

    .Column(new CONCAT("Order #", new Column("OrderID"), " placed on ", new Column("OrderDate"), " by customer ", new Column("CustomerID")), "OrderSummary")  

    .From("Orders")  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT OrderID AS OrderID,
       CONCAT(@pMAIN_2507192016472378950, OrderID, @pMAIN_2507192016472378951, OrderDate, @pMAIN_2507192016472378952, CustomerID) AS OrderSummary
FROM Orders;


            
        

Parameters (If used)

Query Results 4:

5. Usage of CONCAT to create custom order details info.

SQL Server Query 5

            
SELECT TOP 20 OrderID, ProductID,   

	CONCAT('Product ID: ', ProductID, ', Quantity: ', Quantity, ', Unit Price: $', UnitPrice) AS OrderDetailInfo   

FROM [Order Details];  

            
        

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select().Top(20).Columns("OrderID", "ProductID")  

    .Column(new CONCAT("Product ID: ", new Column("ProductID"), ", Quantity: ", new Column("Quantity"), ", Unit Price: $",  

            new Column("UnitPrice")), "OrderDetailInfo")  

    .From("[Order Details]")  

    .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT TOP 20 OrderID,
              ProductID,
              CONCAT(@pMAIN_2507192016472435020, ProductID, @pMAIN_2507192016472435021, Quantity, @pMAIN_2507192016472435022, UnitPrice) AS OrderDetailInfo
FROM [Order Details];


            
        

Parameters (If used)

Query Results 5:

6. Usage of CONCAT with other SQL function.

SQL Server Query 6

            
SELECT CustomerID, CompanyName   

FROM Customers   

WHERE CONCAT(LEFT(CompanyName, 1), RIGHT(CompanyName, 1)) = 'AS';  

            
        

Create SQL query with SqlQueryBuilder 6

            
var (sql6, parameters6) = 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 6

            
SELECT CustomerID,
       CompanyName
FROM Customers
WHERE CONCAT(LEFT(CompanyName, @pMAIN_2507192016472483380), RIGHT(CompanyName, @pMAIN_2507192016472483381)) = @pMAIN_2507192016472483382;


            
        

Parameters (If used)

Query Results 6: