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_2603210348585842380, CompanyName) AS RestaurantPosition
FROM Customers
WHERE PATINDEX(CONCAT(@pMAIN_2603210348585842381, @pMAIN_2603210348585842382, @pMAIN_2603210348585842383), CompanyName) > @pMAIN_2603210348585842384;


            
        

Parameters (If used)

Name Value
@pMAIN_2603210348585842380 %Restaurant%
@pMAIN_2603210348585842381 %
@pMAIN_2603210348585842382 Restaurant
@pMAIN_2603210348585842383 %
@pMAIN_2603210348585842384 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_2603210348585906130, ProductName) > @pMAIN_2603210348585906131 THEN PATINDEX(@pMAIN_2603210348585906132, ProductName) WHEN PATINDEX(@pMAIN_2603210348585906133, ProductName) > @pMAIN_2603210348585906134 THEN PATINDEX(@pMAIN_2603210348585906135, ProductName) ELSE @pMAIN_2603210348585906136 END AS MatchedPosition,
       CASE WHEN PATINDEX(@pMAIN_2603210348585906137, ProductName) > @pMAIN_2603210348585906138 THEN @pMAIN_2603210348585906139 WHEN PATINDEX(@pMAIN_260321034858590613_10, ProductName) > @pMAIN_260321034858590613_11 THEN @pMAIN_260321034858590613_12 ELSE @pMAIN_260321034858590613_13 END AS MatchDescription
FROM Products
WHERE PATINDEX(@pMAIN_260321034858590613_14, ProductName) > @pMAIN_260321034858590613_15
      OR PATINDEX(@pMAIN_260321034858590613_16, ProductName) > @pMAIN_260321034858590613_17;


            
        

Parameters (If used)

Name Value
@pMAIN_2603210348585906130 %Chef [A-Za-z]%
@pMAIN_2603210348585906131 0
@pMAIN_2603210348585906132 %Chef [A-Za-z]%
@pMAIN_2603210348585906133 %[0-9][gG]%
@pMAIN_2603210348585906134 0
@pMAIN_2603210348585906135 %[0-9][gG]%
@pMAIN_2603210348585906136 0
@pMAIN_2603210348585906137 %Chef [A-Za-z]%
@pMAIN_2603210348585906138 0
@pMAIN_2603210348585906139 Chef followed by word
@pMAIN_260321034858590613_10 %[0-9][gG]%
@pMAIN_260321034858590613_11 0
@pMAIN_260321034858590613_12 Number followed by g/G
@pMAIN_260321034858590613_13 No Match
@pMAIN_260321034858590613_14 %Chef [A-Za-z]%
@pMAIN_260321034858590613_15 0
@pMAIN_260321034858590613_16 %[0-9][gG]%
@pMAIN_260321034858590613_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