Other TRANSLATE SQL function

1. Usage of TRANSLATE to format phone number in various way

SQL Server Query 1

            
 SELECT TOP 10  

     CustomerID,  

     ContactName,  

     Phone,  

     TRANSLATE(Phone, '()-+. ', '______') AS NormalizedPhoneNumber,  

     -- Further extraction of numeric parts (requires more complex string manipulation)  

     CASE  

         WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', TRANSLATE(Phone, '()-+. ', '______')) = 0  

             THEN SUBSTRING(TRANSLATE(Phone, '()-+. ', '______'), PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', TRANSLATE(Phone, '()-+. ', '______')), 10)  

         -- Add more complex logic for different phone number patterns if needed  

         ELSE NULL  

     END AS ExtractedPhoneNumber  

 FROM  

     Customers;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(10)  

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

     .Column(new TRANSLATE(new Column("Phone"), "()-+. ", "______"), "NormalizedPhoneNumber")  

     .Column(new CASE()  

         .When(new PATINDEX("%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%", new TRANSLATE(new Column("Phone"), "()-+. ", "______")).Equale(0))  

         .Then(new SUBSTRING(new TRANSLATE(new Column("Phone"), "()-+. ", "______"), new PATINDEX("%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%"  

             , new TRANSLATE(new Column("Phone"), "()-+. ", "______")), 10))  

         .Else(new Column("NULL")), "ExtractedPhoneNumber")  

     .From("Customers")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 CustomerID,
              ContactName,
              Phone,
              TRANSLATE(Phone, @pMAIN_2602032112547043590, @pMAIN_2602032112547043591) AS NormalizedPhoneNumber,
              CASE WHEN PATINDEX(@pMAIN_2602032112547043592, TRANSLATE(Phone, @pMAIN_2602032112547043593, @pMAIN_2602032112547043594)) = @pMAIN_2602032112547043595 THEN SUBSTRING(TRANSLATE(Phone, @pMAIN_2602032112547043596, @pMAIN_2602032112547043597), PATINDEX(@pMAIN_2602032112547043598, TRANSLATE(Phone, @pMAIN_2602032112547043599, @pMAIN_260203211254704359_10)), @pMAIN_260203211254704359_11) ELSE NULL END AS ExtractedPhoneNumber
FROM Customers;


            
        

Parameters (If used)

Query Results 1:

2. Usage of TRANSLATE to mask phone number

SQL Server Query 2

            
 SELECT TOP 10  

     CustomerID,  

     ContactName,  

     Phone,  

     TRANSLATE(Phone, '0123456789', '**********') AS MaskedPhoneNumber  

 FROM  

     Customers;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(10)  

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

     .Column(new TRANSLATE(new Column("Phone"), "0123456789", "**********"), "MaskedPhoneNumber")  

     .From("Customers")  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 10 CustomerID,
              ContactName,
              Phone,
              TRANSLATE(Phone, @pMAIN_2602032112547131000, @pMAIN_2602032112547131001) AS MaskedPhoneNumber
FROM Customers;


            
        

Parameters (If used)

Query Results 2: