Complex Search - 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

SQL Server Development (2000)

Complex Search

Author

Topic

digiduck
Starting Member

2 Posts

Posted - 2003-04-24 : 06:34:39

First off I'm very new to SQL Server. Now, here's my problem. I have a table with two columns, ID (int identity) and TEXT (varchar(8000))

Here is some example data that might be in the TEXT column......
"SQL Rocks{89}, doesn't it? This{7} is awesome!"
"This is Another{945} Row, Awesome{65}!"
"Do You, Get{917}; It? I sure{154}{(76)} I hope{97} so."

Now I need to be able to search for the phrase say, "this is awesome" and find the first row, not the second. The search would ignore anything inside braces {} in the database and would also ignore any punctuation in the database (any , ; ! ? : ect...). It would return both the ID and TEXT columns for each result row.

Now how can I do this? Stored Procedures? Full-Text Index? a combination of both?
Thanks.
-Joshua David

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-24 : 08:37:56

break the search into 3 parts...
search for "This"
search for " is "
search for "awesome"

and make sure that the 3 search terms appear in the above order left to right...use charindex or equivelent to determine the order of each search field.

you will need to add som extra logic to ensure that the likes of "This is truely awesome" is excluded....

hth

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-24 : 08:39:49

How much data?
How often will the searches take place?

If lots and frequently is the answer you'll want to go with full - text indexing and use CONTAINS, FREETEXT, AND FREETEXTTABLE in your where clause.

Edited by - ValterBorges on 04/24/2003 08:42:28

digiduck
Starting Member

2 Posts

Posted - 2003-04-24 : 08:44:43

there are around 31000 records. the table is static and its content won't change.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-24 : 09:09:24

Brain Fart

Can someone explain why this returns 0

DECLARE @string nvarchar

SELECT @string = 'SQL Rocks{89}, doesn''t it? This{7} is awesome!'

SELECT PATINDEX('%This[{]7[}]%is%awesome%', @string)

Edited by - ValterBorges on 04/24/2003 09:10:03

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-24 : 09:27:27

You have nvarchar without the size so it's nvarchar(1)!

Bambola.

Edited by - bambola on 04/24/2003 09:27:48

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 10:18:48

EDIT : this doesn't actually work very well ... never mind. I'll leave it posted below for reference, though.

declare @SearchFor varchar(100);
declare @t table (txt varchar(1000));

insert into @t
select 'SQL Rocks{89}, doesn''t it? This{7} is awesome!' union
select 'This is Another{945} Row, Awesome{65}!' union
select 'Do You, Get{917}; It? I sure{154}{(76)} I hope{97} so.'

set @SearchFor = 'this is awesome'

-- here is the meat of it:

set @SearchFor = '% ' + @SearchFor;
set @SearchFor = replace(@SearchFor, ' ','[{.!;: ]%') + '%'

print @SearchFor

select * from
@t
where txt like @SearchFor

Edited by - jsmith8858 on 04/24/2003 11:10:12

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-25 : 08:24:49

quote:

You have nvarchar without the size so it's nvarchar(1)!

Jsmith8858,

Why do you say your doesn't work?

I've include } and ( and )
set @SearchFor = replace(@SearchFor, ' ','[{}().!;: ]%') + '%'

Edited by - ValterBorges on 04/25/2003 08:31:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources