Wilcard Searches - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Wilcard Searches

Author

Topic

Tonic
Starting Member

26 Posts

Posted - 2006-05-13 : 10:43:32

I have a function that collects a number of peices of information. The one thing that is stopping me currently is the wildcard serach.

As suggest elsewhere, I have tried the following in the WHERE clause but I get an error when I parse the query on the INT field (as marked in Red below.

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+ '%')

Is this because the field is an INT field? if so what is the wildcard that allows INTs?

Regards

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-13 : 10:55:01

[code] (src_centre_list.Portfolio_no like @vch_portfolio_no+ '%') AND [/code]

Tonic
Starting Member

26 Posts

Posted - 2006-05-13 : 11:00:43

Sorry, I must have submitted the wrong peice of code in. I am using it with the Like statement you have suggested and the field that is an INT is the only one it grumbles on.

WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode Like @chr_div+ '%') AND
(src_centre_list.Portfolio_no Like @vch_portfolio_no+ '%') AND
(src_centre_list.propcat Like @vch_prop_cat+ '%')

Sorry about that, my bad

Regards

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-13 : 11:06:14

LIKE works on string column.

You can convert the column to string using convert(varchar(20, yourintcol) and use like on it. However, it will not be efficient as it will cause a table scan even if you have index created for the column.

Can you use something like this ?
for 5 digits numbers beginning with 12

where your_int_col >= 12000  and your_int_col < 13000

Tonic
Starting Member

26 Posts

Posted - 2006-05-13 : 11:09:28

There can only be a value of 1 to 9 in this field. basically the entry is a Portfolio number as you may have gather, so I either want to specify a particluar portfolio, or I want to be able to select all portfolio numbers.

Does that clear it up at all?

Regards

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-13 : 11:13:25

is the number of digits for portfolio number fixed ? If yes, you can use the method i suggested

Tonic
Starting Member

26 Posts

Posted - 2006-05-13 : 11:59:08

It is fixed in the respect of the fact it is a tinyint, and also the fact that it could be a value between 1 or 9, or, it could be a specific value such as 4 (for arguments sake.

Regards

Toni C
aka Talisa

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 00:36:44

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

Toni C
aka Talisa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources