SQLUSA
BI TRIO 2008
FREE TRIAL
CLICK HERE TO ORDER

SQL Server 2008 Best Practices
SQL Server 2005 Best Practices

How to build a stored procedure for wildcard search?

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
 

Subcategory Product ListPrice MaxPrice Search
Bottles and Cages Mountain Bottle Cage $9.99 $650.00 %Mountain%
Fenders Fender Set - Mountain $21.98 $650.00 %Mountain%
Handlebars HL Mountain Handlebars $120.27 $650.00 %Mountain%
Handlebars LL Mountain Handlebars $44.54 $650.00 %Mountain%
Handlebars ML Mountain Handlebars $61.92 $650.00 %Mountain%
Mountain Bikes Mountain-500 Black, 40 $539.99 $650.00 %Mountain%
Mountain Bikes Mountain-500 Black, 42 $539.99 $650.00 %Mountain%
Mountain Bikes Mountain-500 Black, 44 $539.99 $650.00 %Mountain%

 

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

Subcategory Product ListPrice MaxPrice Search
Mountain Bikes Mountain-500 Silver, 40 $564.99 $950.00 Mountain-500 Silver, 4_
Mountain Bikes Mountain-500 Silver, 42 $564.99 $950.00 Mountain-500 Silver, 4_
Mountain Bikes Mountain-500 Silver, 44 $564.99 $950.00 Mountain-500 Silver, 4_
Mountain Bikes Mountain-500 Silver, 48 $564.99 $950.00 Mountain-500 Silver, 4_

*/
-- [](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

Subcategory Product ListPrice MaxPrice Search
Mountain Bikes Mountain-300 Black, 48 $1,079.99 $1,100.00 Mountain-[123456]00 Black, 48
Mountain Bikes Mountain-500 Black, 48 $539.99 $1,100.00 Mountain-[123456]00 Black, 48

*/
-- [^](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

Subcategory Product ListPrice MaxPrice Search
Mountain Bikes Mountain-200 Black, 42 $2,294.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-200 Black, 46 $2,294.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-200 Silver, 42 $2,319.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-200 Silver, 46 $2,319.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-300 Black, 40 $1,079.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-300 Black, 44 $1,079.99 $2,800.00 Mountain-[^4-9]00 %, 4_
Mountain Bikes Mountain-300 Black, 48 $1,079.99 $2,800.00 Mountain-[^4-9]00 %, 4_

*/

------------
 
-- 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

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

 

The World Leader in SQL Server 2008 Training
The future is just a CLICK away. Your future!
 
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.