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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.2 seconds. Powered By: Snitz Forums 2000