SQL Server Where Operator 1

[dhar2611]

I got a idiotic question. in ASP I am doing a key word search using sql statement.

For example:

search for "computer".... results is fine. It gets only the records with the keyword like "%computer%"

search for "computer service"..... results comes out but not the way I want. my sql statement is like "%computer%" or like "%service%".

Its working. But I want to list the records with both keywords first and then list the other records with either keyword matching.

Any Idea....

 

[jbenson001]

You need to try something like:

WHERE <col> like '%computer service%' OR <col> like '%computer%' OR <col> like '%service%'

The Order By will be tricky, but this should get you started.

Jim

 

[SajidAttar]

Here is sample procedure . .

create procedure dbo.sjdSearch (@SearchString varchar(1000) = '') as

set nocount on
set ansi_warnings off

declare @SQLStr nvarchar(4000), @SQLWhr nvarchar(2000)
-- Your Select statement with the mandatory criteria
set @SQLStr = 'Select * from dbo.sysobjects where xtype = ''U'' '
set @SearchString = ltrim(rtrim(isnull(@SearchString, '')))

while charindex(' ', @SearchString) > 0
begin
	set @SQLWhr = IsNull(@SQLWhr + ' Or ', '') + ' name like ''%' + rtrim(left(@SearchString,  charindex(' ', @SearchString)-1)) + '%'' '
	set @SearchString = right (@SearchString, len(@SearchString) -  charindex(' ', @SearchString))
end

if ltrim(@SearchString)<>'' and charindex(' ', @SearchString) = 0
begin
	set @SQLWhr = IsNull(@SQLWhr + ' Or ', '') + ' name like ''%' + @SearchString + '%'' '
end

if ltrim(@SQLWhr) <> ''
	set @SQLStr = @SQLStr + ' and (' + @SQLWhr + ')'

--execute sp_ExecuteSQL @SQLStr
print @SQLStr

Execute sjdSearch '<Table1> <Table2> <.  .  .>'

This will return the table names that are having any of the word from parameters.

you need to do alteration per your requirement.


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[vongrunt]

Regarding sort:

select blah blah,
	case when <col> like '%computer service%' then 2 
		 when <col> like '%computer%' and <col> like '%service%' then 1 
		 else 0 
	end as relevancy
from myTable
where <col> like '%computer%' OR <col> like '%service%'
order by relevancy desc, <someothersort>

That should sort 'computer service' on top, 'computer' and 'service' after that, 'computer' or 'service' at the end.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

vongrunt.. cool solution.. I was thinking along those lines.. but just couldn't get it...

Jim

p.s. I think this needs to be an OR statement though:

when <col> like '%computer%' and <col> like '%service%' then 1

when <col> like '%computer%' OR <col> like '%service%' then 1

 

[vongrunt]

Nope, I assumed that under the rest (else 0).

Rows containing exactly 'Computer service' are the most relevant. Rows having both words (actually substrings) 'computer' AND 'service' come after that. And finally - rows having only one sub-match (either 'computer' OR 'service').

And WHERE clause gives 'em all... I think.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Ahhh you are the man.. thinking even deeper than i was.. very cool.... very thorough

 

[dhar2611]

Hi vongrunt,

I read your code

select blah blah,
case when <col> like '%computer service%' then 2
when <col> like '%computer%' and <col> like '%service%' then 1
else 0
end as relevancy
from myTable
where <col> like '%computer%' OR <col> like '%service%'
order by relevancy desc, <someothersort>

Its a simple solution everyone are saying. But I cant understand. Its got a bit of sql and vba. When I just copied the code and entered the field names it give a syntax error. Could you please explain. How this works.

Thanks.

Dave

 

[vongrunt]

Post query here...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Here is an example just run the whole code in the pubs DB


use pubs

select city,
case when city like '%san jose%' then 2
when city like '%san%' and city like '%jose%' then 1
else 0
end as relevancy
from authors
where city like '%san%' OR city like '%jose%'
order by relevancy desc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dhar2611]

So I should try this on the sql server as a procedure. Can this be tried as vba or asp code.

B'coz it keeps giving error.

Thanks

Dave

 

[SQLDenis]

run it from Query Analyzer


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dhar2611]

Currently I am running the code on a asp page. Is there anyway I can apply this code in my page.

Dave

 

[vongrunt]

Lemme echo myself:

Post code here...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dhar2611]

Here is my sql statement on my asp page

sql = "SELECT BusinessType as Type"
sql = sql & " FROM tblBusiness"
sql = sql & " WHERE tblBusiness.BVerify = 1"
sql = sql & " AND tblBusiness.BDisabled = 0"
sql = sql & " AND tblBusSubType.BActive = 1"
sql = sql & " GROUP BY SubTypeID"
sql = sql & " HAVING (BusinessType like '%Computer%' or BusinessType like %Service%')"
sql = sql & " ORDER BY tblBusSubType.SubTypeID"

objRS.Open sql,objConn,adOpenStatic,adLockReadOnly,adCmdText

I execute the code using the recordset.

Dave

 

[vongrunt]

Originally we talked about searching and some crude form of ranking by relevance.

This query has GROUP BY/HAVING... apples and oranges.

Can you tell us what is that ASP query exactly supposed to do?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dhar2611]

I have modified a bit of the SQL. I am trying to retreive all the business type from the system. When they enter the keyword I search for those records and display on the screen. I have to group them and display on the screen.

sql = "SELECT BusinessType as Type"
sql = sql & " FROM tblBusiness"
sql = sql & " WHERE tblBusiness.BVerify = 1"
sql = sql & " AND tblBusiness.BDisabled = 0"
sql = sql & " AND tblBusSubType.BActive = 1"
sql = sql & " GROUP BY BusinessType "
sql = sql & " HAVING (BusinessType like '%Computer%' or BusinessType like %Service%')"
sql = sql & " ORDER BY tblBusiness.BusinessType

 

[vongrunt]

> I have to group them and display on the screen.

This means = remove duplicates?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dhar2611]

yes. Thanks

 

[vongrunt]

Then use either DISTINCT or GROUP BY without HAVING:

SELECT DISTINCT BusinessType as Type,
	CASE WHEN BusinessType like '%Computer Service%' then 2
		WHEN BusinessType like '%Computer%' or BusinessType like '%Service%' then 1
		ELSE 0 
	END AS relevance
FROM tblBusiness
WHERE BVerify = 1
	AND BDisabled = 0
	AND BActive = 1
	AND (BusinessType like '%Computer%' or BusinessType like '%Service%')
ORDER BY Relevance desc, BusinessType

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)