Other STUFF SQL function

1. Usage of STUFF to mask customer phone number

SQL Server Query 1

            
 SELECT TOP 15  

     CustomerID,  

     ContactName,  

     Phone,  

     STUFF(Phone, 3, LEN(Phone) - 4, REPLICATE('X', LEN(Phone) - 4)) AS AnonymizedPhoneNumber  

 FROM  

     Customers;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(15)  

     .Columns("CustomerID", "ContactName", "Phone")  

     .Column(new STUFF(new Column("Phone"), 3, new LEN(new Column("Phone")).AddArithmatic(new Arithmatic().Subtract(4)),  

         new REPLICATE("X", new LEN(new Column("Phone")).AddArithmatic(new Arithmatic().Subtract(4)))), "AnonymizedPhoneNumber")  

     .From("Customers")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 15 CustomerID,
              ContactName,
              Phone,
              STUFF(Phone, @pMAIN_2507200205126430310, LEN(Phone) - @pMAIN_2507200205126430311, REPLICATE(@pMAIN_2507200205126430312, LEN(Phone) - @pMAIN_2507200205126430313)) AS AnonymizedPhoneNumber
FROM Customers;


            
        

Parameters (If used)

Query Results 1:

2. Usage of STUFF

SQL Server Query 2

            
 SELECT TOP 10  

     o.OrderID,  

     c.CompanyName,  

     (  

         SELECT  

             STUFF((  

                 SELECT ', ' + p.ProductName + ' (Qty: ' + CAST(od.Quantity AS VARCHAR) + ')'  

                 FROM [Order Details] od_inner  

                 JOIN Products p ON od_inner.ProductID = p.ProductID  

                 WHERE od_inner.OrderID = o.OrderID  

                 FOR XML PATH('')  

             ), 1, 2, '')  

     ) AS OrderDetails  

 FROM  

     Orders o  

 JOIN  

     Customers c ON o.CustomerID = c.CustomerID  

 JOIN  

 	[Order Details] od ON o.OrderID = od.OrderID  

 ORDER BY  

     o.OrderID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .Columns("o.OrderID", "c.CompanyName")  

     .Column(new SqlQueryBuilder()  

         .Select()  

         .Column(new STUFF(new Column(new SqlQueryBuilder()  

             .Select()  

             .Column(new CONCAT(", ", new Column("p.ProductName"), "(Qty: ", new CAST(new Column("od.Quantity"), SqlDataType.VARCHAR), ")"), "OrderDetails")  

             .From("[Order Details]", "od_inner")  

             .Join(new List<IJoin>()  

             {  

                 new INNERJOIN().TableName(new Table("Products","p"))  

                     .On(new Column("od_inner.ProductID").Equale(new Column("p.ProductID")))  

             })  

             .Where(new Where(new Column("od_inner.OrderID").Equale(new Column("o.OrderID"))))  

             .ForXml(new FOR_XML_PATH(""))), 1, 2, ""), "OrderDetails")  

     , "OrderDetails")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("Customers","c"))  

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

         new INNERJOIN().TableName(new Table("[Order Details]","od"))  

             .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

     })  

     .OrderBy(new OrderBy().SetColumnAscending("o.OrderID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 10 o.OrderID,
              c.CompanyName,
              (SELECT STUFF((SELECT CONCAT(@pMAIN_2507200205126499220, p.ProductName, @pMAIN_2507200205126499221, CAST (od.Quantity AS VARCHAR), @pMAIN_2507200205126499222)
                             FROM [Order Details] AS od_inner
                                  INNER JOIN
                                  Products AS p
                                  ON od_inner.ProductID = p.ProductID
                             WHERE od_inner.OrderID = o.OrderID
                             FOR XML PATH ('')), @pMAIN_2507200205126499223, @pMAIN_2507200205126499224, @pMAIN_2507200205126499225)) AS OrderDetails
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
ORDER BY o.OrderID ASC;


            
        

Parameters (If used)

Query Results 2: