Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to create and execute the wildcard search stored procedure with different search masks; test PATINDEX with wildcard search mask.

USE AdventureWorks;
GO
 
IF OBJECT_ID('Production.sprocSearchProductList','P') IS NOT NULL
  DROP PROCEDURE Production.sprocSearchProductList;
GO
-- Microsoft SQL Server T-SQL wildcard search - MSSQL wildcard query
-- SQL wildcard syntax - SQL Server stored procedure with parameters
CREATE PROCEDURE Production.sprocSearchProductList
                @Product  VARCHAR(40),
                @MaxPrice MONEY
AS
  SELECT   Subcategory = s.Name,
           Product = p.[Name],
-- SQL Server currency formatting
           ListPrice = '$' + convert(VARCHAR,p.ListPrice),
           MaxPrice = '$' + convert(VARCHAR,@MaxPrice),
           Search = @Product
  FROM     Production.Product AS p
           INNER JOIN Production.ProductSubcategory AS s
             ON p.ProductSubcategoryID = s.ProductSubcategoryID
-- SQL like operator
  WHERE   p.[Name] LIKE @Product
           AND p.ListPrice <= @MaxPrice
  ORDER BY Subcategory,
           Product;
GO
 
-- Execute wildcard search stored procedure - SQL wildcard: %Mountain% - search mask
-- % (percent) matches any substring - with $650 price ceiling
-- Find all products with "Mountain" in the name and top price $650
EXECUTE Production.sprocSearchProductList '%Mountain%', 650
GO
 
/* Partial results
 

*/
-- _ (underscore) matches any ONE character with $950 price limit
EXECUTE Production.sprocSearchProductList 'Mountain-500 Silver, 4_', 950
GO
/* Results
 

*/
-- [](brackets) matches any character listed in brackets: set or range
EXECUTE Production.sprocSearchProductList 'Mountain-[123456]00 Black, 48', 1100
EXECUTE Production.sprocSearchProductList 'Mountain-[1-6]00 Black, 48', 1100

GO

/* Results

*/
-- [^](brackets) matches any character NOT listed in brackets: set or range
EXECUTE Production.sprocSearchProductList 'Mountain-[^456789]00 %, 4_', 2800

EXECUTE Production.sprocSearchProductList 'Mountain-[^4-9]00 %, 4_', 2800
GO

/* Results

*/

------------
 
-- PATINDEX uses the same wildcard search mask as LIKE
-- Filter out {space | - | ( | ) } from phone number such as 1 (11) 500 555-0132
-- Check if (other) non-digit character in phone number starting with position 2
SELECT NoOfAlphaInPhoneNumber = COUNT(*)
FROM   AdventureWorks.Person.Contact
WHERE  PatIndex('%[^0-9]%',
      replace(replace(replace(replace
      (IsNull(Phone,'*'),' ',''),'-',''),'(',''),')','')) > 1
GO
-- 0

------------