Search across two fields returning zero records using "AND"

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

Old Forums

CLOSED - General SQL Server

Search across two fields returning zero records using "AND"

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-29 : 08:25:59

Jason Hill writes "Hi

I am trying to search across two fields in a table using "LIKE", "AND" & wildcards.
The procedures below work using "OR" obviously returning records out of 1 field or another which is not my aim.
When i try "AND" in replacement of "OR" no records are returned.

I am aware that Microsoft say

"A string comparison using a pattern containing char and varchar data may not pass a LIKE comparison because of how the data is stored."

I have tried text as an alternative with the same zero record results. Am I confusing the table by having data stored as varchar then altering the field to text?

The procedures are being created within Enterprise Manager on "service manager 8"

I have been able to create an AND range comparison using money without wildcards and using
"WHERE prop_price > @prop_min AND prop_price < @prop_max"

So I really would appreciate if you could help solve my character problems!!

Kind Regards

Jay

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

CREATE PROCEDURE authuser.p_searchNEWapproach
@prop_town text = '%',
@prop_class text = '%'

AS

SELECT property_id, prop_number, prop_address, prop_class, prop_town

FROM t_property

WHERE prop_class LIKE @prop_class AND prop_town LIKE @prop_class
GO

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

CREATE PROCEDURE authuser.p_search4
@prop_address varchar (400),
@prop_class varchar (100)

AS

SELECT @prop_address = RTRIM(LTRIM (@prop_address))
SELECT @prop_class = RTRIM(LTRIM (@prop_class))
SELECT property_id, prop_number, prop_address, prop_class, prop_town

FROM t_property

WHERE prop_address LIKE '%' + @prop_address + '%' AND prop_class LIKE '%' + @prop_class + '%'
GO

---------------------------------------------------------------------------------------------------------------------"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-29 : 08:54:28

Your query looked like it should work, so do you have some sample data?

I tried the following out and it works.


Declare @myTable table (col1 varchar(100), col2 varchar(100))
Insert Into @myTable
Select 'Apple', 'Blue'
Union All Select 'Green Apple', 'Red'
Union All Select 'Orange', 'Blue-Green'

Declare @search1 varchar(100),
	@search2 varchar(100)

Set @search1 = 'Apple'
Set @search2 = ''

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Set @search1 = ''
Set @search2 = 'Blue'

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Set @search1 = 'Apple'
Set @search2 = 'Red'

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Corey

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-10-29 : 17:17:47

In your "NEW" approach, you have your sproc parameters defined as text data type. I don't think you can actually pass Text data types to and from sprocs. Why not leave them as varchar?

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources