REPLACE SQL function

1. Usage of REPLACE to change product name.

SQL Server Query 1

            
SELECT  

    ProductName,  

    -- Basic replacement: Change 'Chef' to 'Cook'  

    REPLACE(ProductName, 'Chef', 'Cook') AS ReplacedChef,  

    -- Case-insensitive replacement (more complex using nested REPLACE)  

    REPLACE(REPLACE(REPLACE(LOWER(ProductName), 'chef', '#TEMP#'), 'cook', 'chef'), '#TEMP#', 'cook') AS ReplacedChefCaseInsensitive,  

    -- Replacing multiple different substrings  

    REPLACE(REPLACE(ProductName, 'Sauce', 'Dressing'), 'Syrup', 'Topping') AS ReplacedSauceSyrup,  

    -- Replacing based on position (more complex using SUBSTRING and concatenation)  

    CASE  

        WHEN CHARINDEX(' ', ProductName) > 0 THEN  

            LEFT(ProductName, CHARINDEX(' ', ProductName) - 1) +  

            ' - Updated' +  

            SUBSTRING(ProductName, CHARINDEX(' ', ProductName), LEN(ProductName))  

        ELSE  

            ProductName + ' - Updated'  

    END AS ReplacedAfterFirstSpace,  

    -- Removing specific characters (replacing with empty string)  

    REPLACE(ProductName, '''', '') AS RemovedApostrophe  

FROM  

    Products  

            WHERE  

                ProductName LIKE '%Chef%' OR  

                ProductName LIKE '%Sauce%' OR  

                ProductName LIKE '%Syrup%' OR  

                ProductName LIKE '% %' OR  

                ProductName LIKE '%''%';  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column("ProductName", "ProductName")  

    .Column(new REPLACE(new Column("ProductName"), "Chef", "Cook"), "ReplacedChef")  

    .Column(new REPLACE(new REPLACE(new REPLACE(new LOWER(new Column("ProductName")), "chef", "#TEMP#"), "cook", "chef"), "#TEMP#", "cook")  

    , "ReplacedChefCaseInsensitive")  

    .Column(new REPLACE(new REPLACE(new Column("ProductName"), "Sauce", "Dressing"), "Syrup", "Topping"), "ReplacedSauceSyrup")  

    .Column(new CASE()  

        .When(new CHARINDEX(" ", new Column("ProductName")).GreaterThan(0))  

        .Then(new CONCAT(  

                new LEFT(new Column("ProductName"), new CHARINDEX(" ", new Column("ProductName")).AddArithmatic(new Arithmatic().Subtract(1))),  

                " - Updated",  

                new SUBSTRING(new Column("ProductName"), new CHARINDEX(" ", new Column("ProductName")), new LEN(new Column("ProductName")))  

            ))  

        .Else(new CONCAT(new Column("ProductName"), " - Updated"))  

    , "ReplacedAfterFirstSpace")  

    .Column(new REPLACE(new Column("ProductName"), "'", ""), "RemovedApostrophe")  

    .From("Products")  

    .Where(new Where(new LIKE(new Column("ProductName"), "%Chef%"))  

        .OR(new LIKE(new Column("ProductName"), "%Sauce%"))  

        .OR(new LIKE(new Column("ProductName"), "%Syrup%"))  

        .OR(new LIKE(new Column("ProductName"), "% %"))  

        .OR(new LIKE(new Column("ProductName"), "%''%"))  

    )  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductName AS ProductName,
       REPLACE(ProductName, @pMAIN_2507200130333143790, @pMAIN_2507200130333143791) AS ReplacedChef,
       REPLACE(REPLACE(REPLACE(LOWER(ProductName), @pMAIN_2507200130333143792, @pMAIN_2507200130333143793), @pMAIN_2507200130333143794, @pMAIN_2507200130333143795), @pMAIN_2507200130333143796, @pMAIN_2507200130333143797) AS ReplacedChefCaseInsensitive,
       REPLACE(REPLACE(ProductName, @pMAIN_2507200130333143798, @pMAIN_2507200130333143799), @pMAIN_250720013033314379_10, @pMAIN_250720013033314379_11) AS ReplacedSauceSyrup,
       CASE WHEN CHARINDEX(@pMAIN_250720013033314379_12, ProductName, 1) > @pMAIN_250720013033314379_13 THEN CONCAT(LEFT(ProductName, CHARINDEX(@pMAIN_250720013033314379_14, ProductName, 1) - @pMAIN_250720013033314379_15), @pMAIN_250720013033314379_16, SUBSTRING(ProductName, CHARINDEX(@pMAIN_250720013033314379_17, ProductName, 1), LEN(ProductName))) ELSE CONCAT(ProductName, @pMAIN_250720013033314379_18) END AS ReplacedAfterFirstSpace,
       REPLACE(ProductName, @pMAIN_250720013033314379_19, @pMAIN_250720013033314379_20) AS RemovedApostrophe
FROM Products
WHERE ProductName LIKE @pMAIN_250720013033314379_21
      OR ProductName LIKE @pMAIN_250720013033314379_22
      OR ProductName LIKE @pMAIN_250720013033314379_23
      OR ProductName LIKE @pMAIN_250720013033314379_24
      OR ProductName LIKE @pMAIN_250720013033314379_25;


            
        

Parameters (If used)

Query Results 1: