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,079 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    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 No comments