Query a text string. - 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)

Query a text string.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-05-15 : 17:32:11

Hello all....

I have a table that contains 3 columns, Category1, Category2, .....

These categories contain descriptions for computer parts, i.e.:
Category1 = MEMORY
Category2 = DDR
Category3 = 512mb

I need to find a way to query this table so I create a select statement that concatenates the above into one long string:

select Category1+Category2+Categor3 as 'SearchString'
into tempTable
from foobar

I would now like to query this table using terms like:

select * from temptable where [searchstring] like '%ddrmemory%'

The problem is that if my search string is not in the same order as it appears in the temp table then I get no results. Is there a way to have the user submit a query parsed by commas? like, "DDR,MEM" and have the query find all occurances of any of the statements enclosed by commas regardless of the order?

Any help would be much appreciated, thanks.

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 17:39:29

Is this someone searching a web site?
What did the user actually type? ie: "ddrmemory" or "ddr memory"

Peter

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-05-15 : 17:48:22

Yes, searching a webstite, but the search is executed from a stored procedure.

I would like this to work regardless with or without spaces.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 17:52:22

There are lots of ways to tackle this. Good choices depend on

- Are you writing a stored procedure to do this or constructing an ASCII SQL string?

- Are there only 3 columns or more?

Assuming a stored procedure, how about passing each search criteria as a parameter?

create procedure Mysearch
@Parm1 as varchar (20),
@Parm2 as varchar (20)
as
select searchstring

FROM (

SELECT Category1+Category2+Category3 AS searchstring
FROM Foobar

) A

WHERE searchstring like '%'+@Parm1+'%'

AND searchstring like IsNull('%'+@Parm2+'%', searchstring)

go

The IsNull trick in the second compare is always TRUE if @Parm2 is NULL. YOu can add @Parm3 and so on as needed.

Sam

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-05-15 : 17:57:50

How can I set this up where the user can just type:

ddr mem 512 in a text box on a web page?

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 17:58:30

Also, you can try reversing the search:

SELECT * FROM foobar
 WHERE 'ddrmemory' LIKE '%' + Category1 + '%'
 OR 'ddrmemory' LIKE '%' + Category2 + '%'
 OR 'ddrmemory' LIKE '%' + Category3 + '%'

Peter

P.S. Looks like you beat me, Sam, but just in case this helps...

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 18:37:28

I would create a Stored Procedure called something like "WebSiteSearch" with a bunch of optional parameters. Then, I would decide the relevency score criteria like:

Category1 match gives 30 points
Category2 match gives 20 points
Category3 match gives 10 point

Category1 reverse match gives 3 points (they typed ddrmem)
Category2 reverse match gives 2 points (they typed mem512)
Category3 reverse ... etc

Then within the Stored Procedure I would so something like:


SELECT Url FROM (  --BeginQuery FoobarRelevency
SELECT Url, ( --BeginColumnRelevency
(CASE WHEN @Param1 LIKE '%' + Category1 + '%' THEN 30
      WHEN @Param2 LIKE '%' + Category1 + '%' THEN 30
      WHEN @Param3 LIKE '%' + Category1 + '%' THEN 30
      ELSE 0) + 
(CASE WHEN @Param1 LIKE '%' + Category2 + '%' THEN 20
      WHEN @Param2 LIKE '%' + Category2 + '%' THEN 20
      WHEN @Param3 LIKE '%' + Category2 + '%' THEN 20
      ELSE 0) + 
(CASE WHEN @Param1 LIKE '%' + Category3 + '%' THEN 10
      WHEN @Param2 LIKE '%' + Category3 + '%' THEN 10
      WHEN @Param3 LIKE '%' + Category3 + '%' THEN 10
      ELSE 0) + 
(CASE WHEN Category1 LIKE '%' + @Param1 + '%' THEN 3
      WHEN Category1 LIKE '%' + @Param2 + '%' THEN 3
      WHEN Category1 LIKE '%' + @Param2 + '%' THEN 3
      ELSE 0) + 
(CASE WHEN Category2 LIKE '%' + @Param1 + '%' THEN 2
      WHEN Category2 LIKE '%' + @Param2 + '%' THEN 2
      WHEN Category2 LIKE '%' + @Param2 + '%' THEN 2
      ELSE 0) + 
(CASE WHEN Category3 LIKE '%' + @Param1 + '%' THEN 1
      WHEN Category3 LIKE '%' + @Param2 + '%' THEN 1
      WHEN Category3 LIKE '%' + @Param2 + '%' THEN 1
      ELSE 0)) Relevency
FROM Foobar
) FoobarRelevency
WHERE Relevency > 0
ORDER BY Relevency

Peter

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 18:52:25

quote:

How can I set this up where the user can just type:

ddr mem 512 in a text box on a web page?

You'll need to break the tokens apart and insert them into separate parameters.

Assuming @Parm1 is passed containing 'ddr mem 512', you'll need to declare a few other temporary string variables, and parse the tokens into those other variables using string functions. Lookup SUBSTRING and CHARINDEX in BOL.

-- Alternatively, you could parse the tokens in ASP, and pass the parameters to SQL fully separated.

Sam

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 19:06:09

Sam is right. I wasn't thinking straight about how the ASP form would work.
In that case I would recommend changing my procedure to accept one parameter and parse the words there. It will run faster on the DB engine.

Here is a conversation about parsing the words:
[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=26170[/url]

Peter

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-16 : 06:20:25

Have you considered using a Full Text Index on the table? It takes care of lots of things including weights, near matches and rankings.

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 08:00:43

1. You need to create a procedure that accepts a Comma-sepearated (or space seperated) string of words to search for

2. In that procedure, you parse the string passed and put each word into a table variable:

declare @Words table (word varchar(100))
loop through string
insert each word into @Words
..etc...

3. To search your table by a give field for these terms, return the # of terms that match, and a "matching percentage" field, have your stored proc return something like this:

select Items.*,
NumberOfMatches,
NumerOfMatches / (Select COUNT(*) FROM @T) as MatchPct
FROM
Items
INNER JOIN
(
Select Items.ID, Count(*) as NumberOfMatches
FROM ITems
INNER JOIN
@Words W on
Items.Category1 LIKE '%' + W.Word + '%' OR
Items.Category2 LIKE '%' + W.Word + '%' OR
Items.Category3 LIKE '%' + W.Word + '%'
GROUP BY items.ID
)
A
ON A.ID = Items.ID

Hopefully that will give you some ideas. I've posted this method several times so do some searching for more info. There is a UDF out there that'll translate a string into a table variable already written.

Full-text search would be more efficient, but if you just want a simple term searching capability and to be able to return a % match and all that, this is one way to do it.

Let me know if this helps/makes sense.

- Jeff

NOTE: you can do more cool things with this method to, like assume the first word typed always has a slightly greater "weight" than the last or something and add that field to the @words table, and instead of counting the rows that match you do a SUM(Weight) divided by the SUM(weight) for the @words table, etc. Pretty versatile technique...

Edited by - jsmith8858 on 05/16/2003 08:05:03

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-16 : 08:10:23

quote:

I have a table that contains 3 columns, Category1, Category2, .....

These categories contain descriptions for computer parts, i.e.:
Category1 = MEMORY
Category2 = DDR
Category3 = 512mb

Somebody needs to suggest normalization before this thing goes any further ... might as well be me ... We don't want to end up on "Quote of the Week" on dbdebunk.com ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 08:21:02

Good point:

You should have a table of Items, and then a table of ItemCategories (PK of ItemID/CategoryID) which lists 1 row per category for that item. That will help to normalize your database, and you can have unlimited "categories" per item in your database. you can also classify each category with an attribute (since each is a row in a table) that indicated what type it is -- (name, description, size, color, weight, etc).

Then, my search would actually be shorter:

elect Items.*,
NumberOfMatches,
NumerOfMatches / (Select COUNT(*) FROM @T) as MatchPct
FROM
Items
INNER JOIN
(
Select ItemCategories.ID, Count(*) as NumberOfMatches
FROM ITemCategories
INNER JOIN
@Words W on
ItemCategories.Category LIKE '%' + W.Word + '%'
GROUP BY itemCategories.ID
)
A
ON A.ID = Items.ID

- Jeff

Edited by - jsmith8858 on 05/16/2003 09:26:50

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources