PATINDEX SQL function

1. Usage of PATINDEX to find 'restaurant' in company name.

SQL Server Query 1

            
SELECT  

    CustomerID,  

    CompanyName,  

    PATINDEX('%Restaurant%', CompanyName) AS RestaurantPosition  

FROM  

    Customers  

WHERE  

    PATINDEX('%Restaurant%', CompanyName) > 0;

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("CustomerID", "CompanyName")  

    .Column(new PATINDEX("%Restaurant%", new Column("CompanyName")), "RestaurantPosition")  

    .From("Customers")  

    .Where(new Where(new PATINDEX(new CONCAT("%", "Restaurant", "%"), new Column("CompanyName")), SQLComparisonOperators.GREATER_THAN, 0))  

    .Build();

Query build by SqlQueryBuilder 1

            
SELECT CustomerID,
       CompanyName,
       PATINDEX(@pMAIN_2512060922581050960, CompanyName) AS RestaurantPosition
FROM Customers
WHERE PATINDEX(CONCAT(@pMAIN_2512060922581050961, @pMAIN_2512060922581050962, @pMAIN_2512060922581050963), CompanyName) > @pMAIN_2512060922581050964;

Parameters (If used)

Name	Value
@pMAIN_2512060922581050960	%Restaurant%
@pMAIN_2512060922581050961	%
@pMAIN_2512060922581050962	Restaurant
@pMAIN_2512060922581050963	%
@pMAIN_2512060922581050964	0

Query Results 1:

	CustomerID	CompanyName	RestaurantPosition
1	GROSR	GROSELLA-Restaurante	10
2	LONEP	Lonesome Pine Restaurant	15
3	TORTU	Tortuga Restaurante	9

2. Usage of PATINDEX to find position of 'chef' keyword.

SQL Server Query 2

            
SELECT  

    ProductID,  

    ProductName,  

    CASE  

        WHEN PATINDEX('%Chef [A-Za-z]%', ProductName) > 0  

            THEN PATINDEX('%Chef [A-Za-z]%', ProductName)  

        WHEN PATINDEX('%[0-9][gG]%', ProductName) > 0  

            THEN PATINDEX('%[0-9][gG]%', ProductName)  

        ELSE 0  

    END AS MatchedPosition,  

    CASE  

        WHEN PATINDEX('%Chef [A-Za-z]%', ProductName) > 0 THEN 'Chef followed by word'  

        WHEN PATINDEX('%[0-9][gG]%', ProductName) > 0 THEN 'Number followed by g/G'  

        ELSE 'No Match'  

    END AS MatchDescription  

FROM  

    Products  

WHERE  

    PATINDEX('%Chef [A-Za-z]%', ProductName) > 0 OR  

    PATINDEX('%[0-9][gG]%', ProductName) > 0;

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column("ProductID", "ProductID")  

    .Column("ProductName", "ProductName")  

    .Column(new CASE()  

            .When(new PATINDEX("%Chef [A-Za-z]%", new Column("ProductName")).GreaterThan(0))  

            .Then(new PATINDEX("%Chef [A-Za-z]%", new Column("ProductName")))  

            .When(new PATINDEX("%[0-9][gG]%", new Column("ProductName")).GreaterThan(0))  

            .Then(new PATINDEX("%[0-9][gG]%", new Column("ProductName")))  

            .Else(0), "MatchedPosition")  

    .Column(new CASE()  

            .When(new PATINDEX("%Chef [A-Za-z]%", new Column("ProductName")).GreaterThan(0))  

            .Then("Chef followed by word")  

            .When(new PATINDEX("%[0-9][gG]%", new Column("ProductName")).GreaterThan(0))  

            .Then("Number followed by g/G")  

            .Else("No Match"), "MatchDescription")  

    .From("Products")  

    .Where(new Where(new PATINDEX("%Chef [A-Za-z]%", new Column("ProductName")), SQLComparisonOperators.GREATER_THAN, 0)  

        .OR(new PATINDEX("%[0-9][gG]%", new Column("ProductName")), SQLComparisonOperators.GREATER_THAN, 0)  

    )  

    .Build();

Query build by SqlQueryBuilder 2

            
SELECT ProductID AS ProductID,
       ProductName AS ProductName,
       CASE WHEN PATINDEX(@pMAIN_2512060922581115920, ProductName) > @pMAIN_2512060922581115921 THEN PATINDEX(@pMAIN_2512060922581115922, ProductName) WHEN PATINDEX(@pMAIN_2512060922581115923, ProductName) > @pMAIN_2512060922581115924 THEN PATINDEX(@pMAIN_2512060922581115925, ProductName) ELSE @pMAIN_2512060922581115926 END AS MatchedPosition,
       CASE WHEN PATINDEX(@pMAIN_2512060922581115927, ProductName) > @pMAIN_2512060922581115928 THEN @pMAIN_2512060922581115929 WHEN PATINDEX(@pMAIN_251206092258111592_10, ProductName) > @pMAIN_251206092258111592_11 THEN @pMAIN_251206092258111592_12 ELSE @pMAIN_251206092258111592_13 END AS MatchDescription
FROM Products
WHERE PATINDEX(@pMAIN_251206092258111592_14, ProductName) > @pMAIN_251206092258111592_15
      OR PATINDEX(@pMAIN_251206092258111592_16, ProductName) > @pMAIN_251206092258111592_17;

Parameters (If used)

Name	Value
@pMAIN_2512060922581115920	%Chef [A-Za-z]%
@pMAIN_2512060922581115921	0
@pMAIN_2512060922581115922	%Chef [A-Za-z]%
@pMAIN_2512060922581115923	%[0-9][gG]%
@pMAIN_2512060922581115924	0
@pMAIN_2512060922581115925	%[0-9][gG]%
@pMAIN_2512060922581115926	0
@pMAIN_2512060922581115927	%Chef [A-Za-z]%
@pMAIN_2512060922581115928	0
@pMAIN_2512060922581115929	Chef followed by word
@pMAIN_251206092258111592_10	%[0-9][gG]%
@pMAIN_251206092258111592_11	0
@pMAIN_251206092258111592_12	Number followed by g/G
@pMAIN_251206092258111592_13	No Match
@pMAIN_251206092258111592_14	%Chef [A-Za-z]%
@pMAIN_251206092258111592_15	0
@pMAIN_251206092258111592_16	%[0-9][gG]%
@pMAIN_251206092258111592_17	0

Query Results 2:

	ProductID	ProductName	MatchedPosition	MatchDescription
1	4	Chef Anton's Cajun Seasoning	1	Chef followed by word
2	5	Chef Anton's Gumbo Mix	1	Chef followed by word