CODEWITHSUNDEEP
sql-serverstored-proceduresssms
Mike
Mike

Reputation: 435

Wild Cards in a stored procedure

I have a SQL Server database that contains a table address(a_id:int, street:nvarchar(50), apt:nvarchar(50), city:nvarchar(50), state:nvarchar(50), zip:int)

I have a stored procedure that returns the a_id for a specific address.

SELECT
    address_id
FROM
     address a
WHERE
    street like '%'+ @street +'%'
    and apt like '%'+ @apt +'%'
    and zip = @zip

For example if I put in 1060 West Addison Street Chicago IL 60613 I would want to get the same a_id as 1060 W Addison Street Chicago IL 60613 or 1060 W. Addison Street Chicago IL 60613

As you can see, I tried putting wild cards around the street and apt columns to account for differences in abbreviations and periods, but it does not work.

Currently only the first example returns an a_id, as this is what matches whats in the db exactly

Upvotes: 1

Views: 166

Answers (1)

SchmitzIT
SchmitzIT

Reputation: 9552

The LIKE operator does not work like you hope it will. If you use LIKE %Addisson%, it would return both West Addison and W Addison, but it cannot return W Addison if the input is West Addisson.

From MSDN:

% - Any string of zero or more characters. - WHERE title LIKE '%computer%' 
finds all book titles with the word 'computer' anywhere in the book title.

For your scenario, you might want to check out full tezxt index search, which might allow you to accomplish your goal. Specifically, the CONSTAINABLE keyword might help you out:

Searching for Words or Phrases Using Weighted Values (Weighted Term)

You can use CONTAINSTABLE to search for words or phrases and specify a weighting value. Weight, measured as a number from 0.0 through 1.0, indicates the importance of each word and phrase within a set of words and phrases. A weight of 0.0 is the lowest, and a weight of 1.0 is the highest.

The following example shows a query that searches for all customer addresses, using weights, in which any text beginning with the string "Bay" has either "Street" or "View". The results give a higher rank to those rows that contain more of the words specified.

USE AdventureWorks2012 GO

SELECT AddressLine1, KEY_TBL.RANK  FROM Person.Address AS Address
INNER JOIN CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT
("Bay*", 
         Street WEIGHT(0.9), 
         View WEIGHT(0.1)
         ) ' ) AS KEY_TBL ON Address.AddressID = KEY_TBL.[KEY] ORDER BY 
KEY_TBL.RANK DESC GO

A weighted term can be used in conjunction with any simple term, prefix term, generation term, or proximity term.

Source: Query with Full-Text Search

Upvotes: 1

Related Questions