Wildcard Searches

Question

Post reply

Wildcard Searches

Toni Chaffin

SSCommitted

Points: 1513
More actions
May 13, 2006 at 3:12 am

#113437

I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.
All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.
DECLARE @return_value int
EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'04/28/2006',
@chr_div
= N'NE',
@vch_portfolio_no
= 3,
@vch_prop_cat
= N'core'
SELECT
'Return Value' = @return_value
GO
How can I set this so that it will wild card the value. For example rather than having to specify
@chr_div = N'NE', I could specify something like
@chr_div =
N *, so it would show both NE and SW values in the result set.
Anybody point me in a direction here. I have tried % but that does not seem to work, I get a
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '%'.
Thank in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 1

Currently my WHERE statements look like this;

FROM src_terrier INNER JOIN

src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN

src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN

src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

WHERE (src_terrier.datadate = @dt_src_date) AND

(src_terrier.Areacode = @chr_div) AND

(src_centre_list.Portfolio_no = @vch_portfolio_no) AND

(src_centre_list.propcat = @vch_prop_cat)

I have tried 'Like' in the following context

Like (src_terrier.Areacode = @chr_div)

and also (Like src_terrier.Areacode = @chr_div)

Both of which errored when I tried to Parse the SQL and said it did not like the Like part

Regards

Lowell SSC Guru Points: 323518 More actions · Answer 2

your syntax is incorrect:

AND (src_terrier.Areacode LIKE @chr_div+ '%')

so if @chr_div = 'EV', the addition of the % sign makes the wild card search anything that STARTS with 'EV', like 'evansville' or 'EVERYTHING' (assuming a non-case sensitive coallation)

if you wanted everything that has 'EV' in it:

'%' + @chr_div + '%'

ends with 'EV': '%' + @chr_div

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 3

Ahh, Thanks for that. I will put that in my code.

Another day another snippit of useful information

Thank You

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 4

I just tried that and it errored if I did not put a value in for the datatype that is an INT. If % is the wildacrd for text, what is the wildcard for numbers?

Regards

CK-203762 Newbie Points: 3 More actions · Answer 5

What's the use of wildcard in numbers. There is no special meaning to the less significant digits. For the first numbers you can try '>'.

If the field is 5 digits, and you need to find zip code of NY (and you don't use text for zip code), try: zip > 10000 or between the appropriate range.

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 6

Toni Chaffin

SSCommitted

Points: 1513

May 13, 2006 at 10:15 pm

#637732

Noted and Thank you

Regards

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 7

I have gone for this as the final solution to the problem.

WHERE

(src_terrier.datadate = @dt_src_date) AND

(@chr_div is null or src_terrier.Areacode = @chr_div) AND

(@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND

(@vch_prop_cat is null or src_centre_list.propcat = @vch_prop_cat)

Thanks for the help. Tech Support on a weekend is great

stonemw SSC Veteran Points: 204 More actions · Answer 8

To do a 'wildcard' for numbers, you can default the number to say -1 then in the where clause you can put something like this for your areacode or any other integer provided the default value is never used:

WHERE (src_terrier.Areacode = @chr_div OR @chr_div = -1) AND ....

Ian Yates SSCoach Points: 19738 More actions · Answer 9

If you expect to do a wildcard for numbers like

"all numbers beginning with 199", then you will need to cast the numbers to a varchar and feed this into a "like" expression. But this will be slow If the number is never going to be added, subtracted, etc (like a postcode or a phone number for example) then you should store it as a char-style field.

As for your other code

<SNIP>

WHERE (src_terrier.datadate = @dt_src_date) AND

(@chr_div is null or src_terrier.Areacode = @chr_div) AND

(@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND

(@vch_prop_cat is null or src_centre_list.propcat = @vch_prop_cat)

</SNIP>

Make sure you put extra brackets in there to ensure you never mix up the precedence of AND vs OR, etc. Probably not strictly necessary but I have been caught out before when leaving the brackets out - SQL Server accepts the query and happily processes the conditions in the wrong order

EG ((@chr_div is null) or (src_terrier.Areacode = @chr_div))

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 10

Thank you very much all of you for your detailed posts. Evry day on here is like a day at school.

Great info, thank you

Regards