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.

-- SQL Like Operator quick syntax - SQL LIKE statement

SELECT ProductName=Name, ListPrice

FROM AdventureWorks2008.Production.Product

WHERE Name like '%touring%' ORDER BY ListPrice DESC

/* ProductName          ListPrice

Touring-1000 Blue, 46   2384.07

Touring-1000 Blue, 50   2384.07

Touring-1000 Blue, 54   2384.07

....

*/

USE AdventureWorks2008;
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  1 = 1

           AND (@Product is null or p.[Name] LIKE @Product)     -- NULL parm is OK

           AND (@MaxPrice is null or p.ListPrice <= @MaxPrice)  -- NULL parm is OK

  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

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