SUBSTRING SQL function

1. Usage of SUBSTRING to add - in between 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_2507192019007391020, @pMAIN_2507192019007391021) AS ReplacedChef,
       REPLACE(REPLACE(REPLACE(LOWER(ProductName), @pMAIN_2507192019007391022, @pMAIN_2507192019007391023), @pMAIN_2507192019007391024, @pMAIN_2507192019007391025), @pMAIN_2507192019007391026, @pMAIN_2507192019007391027) AS ReplacedChefCaseInsensitive,
       REPLACE(REPLACE(ProductName, @pMAIN_2507192019007391028, @pMAIN_2507192019007391029), @pMAIN_250719201900739102_10, @pMAIN_250719201900739102_11) AS ReplacedSauceSyrup,
       CASE WHEN CHARINDEX(@pMAIN_250719201900739102_12, ProductName, 1) > @pMAIN_250719201900739102_13 THEN CONCAT(LEFT(ProductName, CHARINDEX(@pMAIN_250719201900739102_14, ProductName, 1) - @pMAIN_250719201900739102_15), @pMAIN_250719201900739102_16, SUBSTRING(ProductName, CHARINDEX(@pMAIN_250719201900739102_17, ProductName, 1), LEN(ProductName))) ELSE CONCAT(ProductName, @pMAIN_250719201900739102_18) END AS ReplacedAfterFirstSpace,
       REPLACE(ProductName, @pMAIN_250719201900739102_19, @pMAIN_250719201900739102_20) AS RemovedApostrophe
FROM Products
WHERE ProductName LIKE @pMAIN_250719201900739102_21
      OR ProductName LIKE @pMAIN_250719201900739102_22
      OR ProductName LIKE @pMAIN_250719201900739102_23
      OR ProductName LIKE @pMAIN_250719201900739102_24
      OR ProductName LIKE @pMAIN_250719201900739102_25;


            
        

Parameters (If used)

Query Results 1:

2. Usage of SUBSTRING to mask company name.

SQL Server Query 2

            
SELECT  

    c.CustomerID,  

    c.CompanyName,  

    REVERSE(c.CompanyName) AS ReversedCompanyName,	  

    SUBSTRING(c.CompanyName, 1, 1) + REPLICATE('*', LEN(c.CompanyName) - 2) +  

    CASE WHEN LEN(c.CompanyName) > 1 THEN REVERSE(SUBSTRING(REVERSE(c.CompanyName), 1, 1)) ELSE '' END AS FirstLastReversedMasked,  

    o.OrderID,  

    REVERSE(CAST(o.OrderID AS VARCHAR)) AS ReversedOrderID  

FROM  

    Customers c  

LEFT JOIN  

    Orders o ON c.CustomerID = o.CustomerID  

WHERE  

    LEN(c.CompanyName) > 3 AND (o.OrderID BETWEEN 10316 AND 10319 OR o.OrderID BETWEEN 10398 AND 10401 OR  

                    o.OrderID BETWEEN 10490 AND 10493 OR o.OrderID BETWEEN 10583 AND 10586 OR  

                    o.OrderID BETWEEN 10686 AND 10689 OR o.OrderID BETWEEN 10806 AND 10809 OR  

                    o.OrderID BETWEEN 10988 AND 10991)  

ORDER BY  

    c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column("c.CustomerID", "CustomerID")  

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

    .Column(new REVERSE(new Column("c.CompanyName")), "ReversedCompanyName")  

    .Column(new CONCAT(  

            new SUBSTRING(new Column("c.CompanyName"), 1, 1),  

            new REPLICATE("*", new LEN(new Column("c.CompanyName")).AddArithmatic(new Arithmatic().Subtract(2))),  

            new CASE()  

                .When(new LEN(new Column("c.CompanyName")).GreaterThan(1))  

                .Then(new REVERSE(new SUBSTRING(new REVERSE(new Column("c.CompanyName")), 1, 1)))  

                .Else("")  

        ), "FirstLastReversedMasked")  

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

    .Column(new REVERSE(new CAST(new Column("o.OrderID"), SqlDataType.VARCHAR)), "ReversedOrderID")  

    .From("Customers", "c")  

    .Join(new List<IJoin>()  

    {  

        new LEFTJOIN().TableName(new Table("Orders","o"))  

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

    })  

    .Where(new Where(new LEN(new Column("c.CompanyName")).GreaterThan(3))  

        .AND().StartBracket().Compare(new BETWEEN(new Column("o.OrderID"), "10316", "10319"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10398", "10401"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10490", "10493"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10583", "10586"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10686", "10689"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10806", "10809"))  

            .OR(new BETWEEN(new Column("o.OrderID"), "10988", "10991"))  

            .EndBracket()  

    )  

    .OrderBy(new OrderBy().SetColumnAscending("c.CustomerID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT c.CustomerID AS CustomerID,
       c.CompanyName AS CompanyName,
       REVERSE(c.CompanyName) AS ReversedCompanyName,
       CONCAT(SUBSTRING(c.CompanyName, @pMAIN_2507192019007451820, @pMAIN_2507192019007451821), REPLICATE(@pMAIN_2507192019007451822, LEN(c.CompanyName) - @pMAIN_2507192019007451823), CASE WHEN LEN(c.CompanyName) > @pMAIN_2507192019007451824 THEN REVERSE(SUBSTRING(REVERSE(c.CompanyName), @pMAIN_2507192019007451825, @pMAIN_2507192019007451826)) ELSE @pMAIN_2507192019007451827 END) AS FirstLastReversedMasked,
       o.OrderID AS OrderID,
       REVERSE(CAST (o.OrderID AS VARCHAR)) AS ReversedOrderID
FROM Customers AS c
     LEFT OUTER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
WHERE LEN(c.CompanyName) > @pMAIN_2507192019007451828
      AND (o.OrderID BETWEEN @pMAIN_2507192019007451829 AND @pMAIN_250719201900745182_10
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_11 AND @pMAIN_250719201900745182_12
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_13 AND @pMAIN_250719201900745182_14
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_15 AND @pMAIN_250719201900745182_16
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_17 AND @pMAIN_250719201900745182_18
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_19 AND @pMAIN_250719201900745182_20
           OR o.OrderID BETWEEN @pMAIN_250719201900745182_21 AND @pMAIN_250719201900745182_22)
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of SUBSTRING to make customer id encoded.

SQL Server Query 3

            
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 3

            
var (sql3, parameters3) = 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 3

            
SELECT TOP 15 CustomerID AS CustomerID,
              CONCAT(SUBSTRING(CustomerID, @pMAIN_2507192019007653220, @pMAIN_2507192019007653221), CASE SUBSTRING(CustomerID, @pMAIN_2507192019007653222, @pMAIN_2507192019007653223) WHEN @pMAIN_2507192019007653224 THEN NCHAR(8524) WHEN @pMAIN_2507192019007653225 THEN NCHAR(401) WHEN @pMAIN_2507192019007653226 THEN NCHAR(8490) ELSE SUBSTRING(CustomerID, @pMAIN_2507192019007653227, @pMAIN_2507192019007653228) END, SUBSTRING(CustomerID, @pMAIN_2507192019007653229, @pMAIN_250719201900765322_10)) AS EncodedCustomerID
FROM Customers;


            
        

Parameters (If used)

Query Results 3:

4. Usage of SUBSTRING to extract first character of second word from company name.

SQL Server Query 4

            
SELECT CustomerID, CompanyName,   

	SUBSTRING(CompanyName, 1, CHARINDEX(' ', CompanyName) - 1) AS FirstWord   

FROM Customers   

WHERE CHARINDEX(' ', CompanyName) > 0; -- Only show companies with at least one space  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select().Columns("CustomerID", "CompanyName")  

    .Column(new SUBSTRING(new Column("CompanyName"), 1, new CHARINDEX(" ", new Column("CompanyName"), 1).AddArithmatic(new Arithmatic().Subtract(-1))), "FirstWord")  

    .From("Customers")  

    .Where(new Where(new CHARINDEX(" ", new Column("CompanyName")).GreaterThan(0)))  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT CustomerID,
       CompanyName,
       SUBSTRING(CompanyName, @pMAIN_2507192019007699530, CHARINDEX(@pMAIN_2507192019007699531, CompanyName, 1) - @pMAIN_2507192019007699532) AS FirstWord
FROM Customers
WHERE CHARINDEX(@pMAIN_2507192019007699533, CompanyName, 1) > @pMAIN_2507192019007699534;


            
        

Parameters (If used)

Query Results 4:

5. Usage of SUBSTRING to make partial OrderID and utilize in join.

SQL Server Query 5

            
WITH CustomerPhoneSuffix AS (  

    SELECT  

        CustomerID,  

        CompanyName,  

        Phone,  

        -- Extract the last 4 digits of the phone number  

        RIGHT(Phone, 4) AS LastFourDigits  

    FROM  

        Customers  

    WHERE  

        LEN(Phone) >= 4  

),  

CustomerWithFaxSuffix AS (  

    SELECT  

        CustomerID,  

        CompanyName,  

        Fax,  

        -- Extract the last 3 digits of the fax number if it exists  

        CASE  

            WHEN Fax IS NOT NULL AND LEN(Fax) >= 3 THEN RIGHT(Fax, 3)  

            ELSE NULL  

        END AS LastThreeFaxDigits  

    FROM  

        Customers  

),  

OrderDetailsWithUnitPriceSuffix AS (  

    SELECT  

        od.OrderID,  

        p.ProductName,  

        od.UnitPrice,  

        -- Extract the last digit of the UnitPrice  

        RIGHT(CAST(FLOOR(od.UnitPrice) AS VARCHAR), 1) AS UnitPriceLastDigit  

    FROM  

        [Order Details] od  

    JOIN  

        Products p ON od.ProductID = p.ProductID  

)  

SELECT  

    c.CustomerID,  

    c.CompanyName,  

    cps.LastFourDigits AS PhoneLastFour,  

    cfx.LastThreeFaxDigits AS FaxLastThree,  

    oud.OrderID,  

    oud.ProductName,  

    oud.UnitPrice,  

    oud.UnitPriceLastDigit  

FROM  

    Customers c  

LEFT JOIN  

    CustomerPhoneSuffix cps ON c.CustomerID = cps.CustomerID  

LEFT JOIN  

    CustomerWithFaxSuffix cfx ON c.CustomerID = cfx.CustomerID  

LEFT JOIN  

    OrderDetailsWithUnitPriceSuffix oud ON c.CustomerID = SUBSTRING(CAST(oud.OrderID AS VARCHAR), 1, 5) -- Attempting a join based on partial OrderID (illustrative, might not be logically sound)  

WHERE  

    cps.LastFourDigits IS NOT NULL OR cfx.LastThreeFaxDigits IS NOT NULL OR oud.UnitPriceLastDigit IS NOT NULL  

ORDER BY  

    c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .WithCTETable(new Table("CustomerPhoneSuffix"), new SqlQueryBuilder()  

        .Select()  

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

        .Column(new RIGHT(new Column("Phone"), 4), "LastFourDigits")  

        .From("Customers")  

        .Where(new Where(new LEN(new Column("Phone")).GreaterThanOrEqualeTo(4)))  

    )  

    .WithCTETable(new Table("CustomerWithFaxSuffix"), new SqlQueryBuilder()  

        .Select()  

        .Columns("CustomerID", "CompanyName", "Fax")  

        .Column(new CASE()  

            .When(new IS_NOT_NULL(new Column("Fax")).AND(new LEN(new Column("FAX")).GreaterThanOrEqualeTo(3)))                          

            .Then(new RIGHT(new Column("Fax"), 3))  

            .Else(new Column().SetNull())  

        , "LastThreeFaxDigits")  

        .From("Customers")  

    )  

    .WithCTETable(new Table("OrderDetailsWithUnitPriceSuffix"), new SqlQueryBuilder()  

        .Select()  

        .Columns("od.OrderID", "p.ProductName", "od.UnitPrice")  

        .Column(new RIGHT(new CAST(new FLOOR(new Column("od.UnitPrice")), SqlDataType.VARBINARY), 1), "UnitPriceLastDigit")  

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

        .Join(new List<IJoin>() {  

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

                .On(new Column("od.ProductID"), SQLComparisonOperators.EQUALE_TO, new Column("p.ProductID"))  

        })  

    )  

    .Select()  

    .Columns("c.CustomerID", "c.CompanyName")  

    .Column("cps.LastFourDigits", "PhoneLastFour")  

    .Column("cfx.LastThreeFaxDigits", "FaxLastThree")  

    .Columns("oud.OrderID", "oud.ProductName", "oud.UnitPrice", "oud.UnitPriceLastDigit")  

    .From("Customers", "c")  

    .Join(new List<IJoin>()  

    {  

        new LEFTJOIN().TableName(new Table("CustomerPhoneSuffix","cps"))  

            .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("cps.CustomerID")),  

        new LEFTJOIN().TableName(new Table("CustomerWithFaxSuffix","cfx"))  

            .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("cfx.CustomerID")),  

        new LEFTJOIN().TableName(new Table("OrderDetailsWithUnitPriceSuffix","oud"))  

            .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new SUBSTRING(new CAST(new Column("oud.OrderID"), SqlDataType.VARCHAR),1,5))  

    })  

    .Where(new Where(new IS_NOT_NULL(new Column("cps.LastFourDigits")))  

        .OR(new IS_NOT_NULL(new Column("cfx.LastThreeFaxDigits")))  

        .OR(new IS_NOT_NULL(new Column("oud.UnitPriceLastDigit")))  

    )  

    .OrderBy(new OrderBy().Set(new Column("c.CustomerID")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 5

            
WITH CustomerPhoneSuffix
AS (SELECT CustomerID,
           CompanyName,
           Phone,
           RIGHT(Phone, @pMAIN_2507192019007747060) AS LastFourDigits
    FROM Customers
    WHERE LEN(Phone) >= @pMAIN_2507192019007747061),
 CustomerWithFaxSuffix
AS (SELECT CustomerID,
           CompanyName,
           Fax,
           CASE WHEN Fax IS NOT NULL
                     AND LEN(FAX) >= @pMAIN_2507192019007747062 THEN RIGHT(Fax, @pMAIN_2507192019007747063) ELSE NULL END AS LastThreeFaxDigits
    FROM Customers),
 OrderDetailsWithUnitPriceSuffix
AS (SELECT od.OrderID,
           p.ProductName,
           od.UnitPrice,
           RIGHT(CAST (FLOOR(od.UnitPrice) AS VARBINARY), @pMAIN_2507192019007747064) AS UnitPriceLastDigit
    FROM [Order Details] AS od
         INNER JOIN
         Products AS p
         ON od.ProductID = p.ProductID)
SELECT c.CustomerID,
       c.CompanyName,
       cps.LastFourDigits AS PhoneLastFour,
       cfx.LastThreeFaxDigits AS FaxLastThree,
       oud.OrderID,
       oud.ProductName,
       oud.UnitPrice,
       oud.UnitPriceLastDigit
FROM Customers AS c
     LEFT OUTER JOIN
     CustomerPhoneSuffix AS cps
     ON c.CustomerID = cps.CustomerID
     LEFT OUTER JOIN
     CustomerWithFaxSuffix AS cfx
     ON c.CustomerID = cfx.CustomerID
     LEFT OUTER JOIN
     OrderDetailsWithUnitPriceSuffix AS oud
     ON c.CustomerID = SUBSTRING(CAST (oud.OrderID AS VARCHAR), @pMAIN_2507192019007747065, @pMAIN_2507192019007747066)
WHERE cps.LastFourDigits IS NOT NULL
      OR cfx.LastThreeFaxDigits IS NOT NULL
      OR oud.UnitPriceLastDigit IS NOT NULL
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 5: