datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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.

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

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

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

 

Exam Prep 70-461
Exam 70-461