SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Search across two fields returning zero records using "AND"
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2004 :  08:25:59  Show Profile  Visit AskSQLTeam's Homepage
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/29/2004 :  08:54:28  Show Profile  Visit Seventhnight's Homepage
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/29/2004 :  17:17:47  Show Profile  Visit AjarnMark's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000