Share via

Using PATINDEX/CHARINDEX from a table of rules

Sherlan Emmanuel Burgonia 86 Reputation points
2022-10-18T03:20:20.22+00:00

Hi I have been working with search patterns from this PTEST table
251403-image.png and I would like to know is there a way that I can use this table instead of hardcoding each to my script? 

SELECT   
	CARNUMCOD,  
	TicketedFareBasis,  
	TourCode,  
	ExpandedFB,  
	ExpandedFB2,  
	PATINDEX(PTEST, ExpandedFB) AS TEST  
FROM EDW_ASB.RAPID_FareBasis
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2022-10-18T21:59:15.123+00:00

    As Niko says, it is not clear what you are looking for. But if you want to find rows which matches any of the patterns: 

       SELECT *  
   FROM  tbl t  
   WHERE EXISTS (SELECT *  
                       FROM  @patterns p   
                       WHERE t.ExpandedFB LIKE t.pattern)
    0 comments