Other NCHAR SQL function

1. Usage of NCHAR to format company name

SQL Server Query 1

            
 SELECT TOP 5  

     CustomerID,  

     CompanyName + ' (' + NCHAR(169) + ')' AS CompanyNameWithCopyright  

 FROM  

     Customers;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(5)  

     .Columns("CustomerID")  

     .Column(new CONCAT(new Column("CompanyName"), " ", new NCHAR(169)), "CompanyNameWithCopyright")  

     .From("Customers")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 5 CustomerID,
             CONCAT(CompanyName, @pMAIN_2507192103021767430, NCHAR(169)) AS CompanyNameWithCopyright
FROM Customers;


            
        

Parameters (If used)

Query Results 1:

2. Usage of NCHAR to to format product info

SQL Server Query 2

            
 SELECT  

     'Product: ' + ProductName + ' ' + NCHAR(0x200E) + ' (Description)' AS FormattedProductInfo  

 FROM  

     Products  

 WHERE ProductID IN (1, 2);  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column(new CONCAT("Product: ", new Column("ProductName"),  

         " ", new NCHAR(0x200E), " (Description)"), "FormattedProductInfo")  

     .From("Products")  

     .Where(new Where(new IN(new Column("ProductID"), 1, 2)))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CONCAT(@pMAIN_2507192103021794110, ProductName, @pMAIN_2507192103021794111, NCHAR(8206), @pMAIN_2507192103021794112) AS FormattedProductInfo
FROM Products
WHERE ProductID IN (@pMAIN_2507192103021794113, @pMAIN_2507192103021794114);


            
        

Parameters (If used)

Query Results 2:

3. Usage of NCHAR to show formatted currency

SQL Server Query 3

            
 SELECT  

     o.OrderID,  

     c.CompanyName,  

     o.OrderDate,  

     od.UnitPrice * od.Quantity AS TotalAmount,   

     CASE  

         WHEN c.Country = 'USA' THEN '$' + CAST(od.UnitPrice * od.Quantity AS NVARCHAR)  

         WHEN c.Country = 'France' THEN NCHAR(0x20AC) + ' ' + CAST(od.UnitPrice * od.Quantity AS NVARCHAR) -- Euro symbol  

         WHEN c.Country = 'Japan' THEN NCHAR(0x00A5) + CAST(od.UnitPrice * od.Quantity AS NVARCHAR) -- Yen symbol  

         ELSE '$' + CAST(od.UnitPrice * od.Quantity AS NVARCHAR) -- Default to USD  

     END AS LocalizedTotal  

 FROM  

     Orders o  

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

 JOIN  

     Customers c ON o.CustomerID = c.CustomerID  

 WHERE o.OrderID IN (10248, 10249, 10250);  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("o.OrderID", "OrderID")  

     .Column("c.CompanyName", "CompanyName")  

     .Column("o.OrderDate", "OrderDate")  

     .Column(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity"), "TotalAmount")  

     .Column(new CASE()  

         .When(new Column("c.Country").Equale("USA"))  

         .Then(new CONCAT("$", new CAST(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity"), SqlDataType.NVARCHAR)))  

         .When(new Column("c.Country").Equale("France"))  

         .Then(new CONCAT(new NCHAR(0x20AC), " ", new CAST(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity"), SqlDataType.NVARCHAR)))  

         .When(new Column("c.Country").Equale("Japan"))  

         .Then(new CONCAT(new NCHAR(0x00A5), " ", new CAST(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity"), SqlDataType.NVARCHAR)))  

         .Else(new CONCAT("$", new CAST(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity"), SqlDataType.NVARCHAR)))  

     , "LocalizedTotal")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()  

     {  

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

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

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

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

     })  

     .Where(new Where(new IN(new Column("o.OrderID"), 10248, 10249, 10250)))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT o.OrderID AS OrderID,
       c.CompanyName AS CompanyName,
       o.OrderDate AS OrderDate,
       od.UnitPrice * od.Quantity AS TotalAmount,
       CASE WHEN c.Country = @pMAIN_2507192103021822720 THEN CONCAT(@pMAIN_2507192103021822721, CAST (od.UnitPrice * od.Quantity AS NVARCHAR)) WHEN c.Country = @pMAIN_2507192103021822722 THEN CONCAT(NCHAR(8364), @pMAIN_2507192103021822723, CAST (od.UnitPrice * od.Quantity AS NVARCHAR)) WHEN c.Country = @pMAIN_2507192103021822724 THEN CONCAT(NCHAR(165), @pMAIN_2507192103021822725, CAST (od.UnitPrice * od.Quantity AS NVARCHAR)) ELSE CONCAT(@pMAIN_2507192103021822726, CAST (od.UnitPrice * od.Quantity AS NVARCHAR)) END AS LocalizedTotal
FROM Orders AS o
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
WHERE o.OrderID IN (@pMAIN_2507192103021822727, @pMAIN_2507192103021822728, @pMAIN_2507192103021822729);


            
        

Parameters (If used)

Query Results 3:

4. Usage of NCHAR to show encoded customer ids

SQL Server Query 4

            
 SELECT TOP 15  

     CustomerID,  

     SUBSTRING(CustomerID, 1, 1) +  

     CASE SUBSTRING(CustomerID, 2, 1)  

         WHEN 'L' THEN NCHAR(0x214C) -- Script L  

         WHEN 'F' THEN NCHAR(0x191)  -- Latin Capital Letter F with Hook  

         WHEN 'K' THEN NCHAR(0x212A) -- Kelvin Sign  

         ELSE SUBSTRING(CustomerID, 2, 1)  

     END +  

     SUBSTRING(CustomerID, 3, 5) AS EncodedCustomerID  

 FROM  

     Customers;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select().Top(15)  

     .Column("CustomerID", "CustomerID")  

     .Column(new CONCAT(  

         new SUBSTRING(new Column("CustomerID"), 1, 1),  

         new CASE(new SUBSTRING(new Column("CustomerID"), 2, 1))  

             .When("L").Then(new NCHAR(0x214C))  

             .When("F").Then(new NCHAR(0x191))  

             .When("K").Then(new NCHAR(0x212A))  

             .Else(new SUBSTRING(new Column("CustomerID"), 2, 1)),  

         new SUBSTRING(new Column("CustomerID"), 3, 5)  

     ), "EncodedCustomerID")  

     .From("Customers")  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT TOP 15 CustomerID AS CustomerID,
              CONCAT(SUBSTRING(CustomerID, @pMAIN_2507192103021955320, @pMAIN_2507192103021955321), CASE SUBSTRING(CustomerID, @pMAIN_2507192103021955322, @pMAIN_2507192103021955323) WHEN @pMAIN_2507192103021955324 THEN NCHAR(8524) WHEN @pMAIN_2507192103021955325 THEN NCHAR(401) WHEN @pMAIN_2507192103021955326 THEN NCHAR(8490) ELSE SUBSTRING(CustomerID, @pMAIN_2507192103021955327, @pMAIN_2507192103021955328) END, SUBSTRING(CustomerID, @pMAIN_2507192103021955329, @pMAIN_250719210302195532_10)) AS EncodedCustomerID
FROM Customers;


            
        

Parameters (If used)

Query Results 4: