Hi all,

Im working in content management system.
using asp.net with c#, sql server 2000.
having doubt how to search a text in a table.

for ex:
my table contains 2 cols one is “id” column and the another one is “content” column(Text data type).
I want to search a text in my content,
it should return the sentence which contains the searching text.

This is one of the content in my table.

****************
"We have spoken about the mobile Web and how different people would be
accessing the Web at different times and on different devices,
a very great diversity. You have a screen with 3 million
pixels one moment and would have a 3 inch screen the next moment.
But it is important that if I refer to something like a train
timetable for example and if I bookmark it using my phone,
I can view it on my computer screen. "
*****************

similarly i have more than 100 content in my table....

if i search the text "3 million", it should return the above sentence which contain the
searching text.

Result:
-------
You have a screen with 3 million pixels one moment and would have a 3 inch screen the next moment

Can any one help me out...


Cheers
Sankar

__________________
Cheers
Sankar

Has someone ordered you to do this or is this your idea?

Richard

It is one of the requirement in my project.

Cheers
Sankar

Well, just doing
    SELECT * FROM table WHERE textfield LIKE '%3 million%'
will get you the relevant records.

But getting the actual *sentence* that contains "3 million" is better done in a regular expression in your C# code.

Thanks Pedant.

I have got an partial solution, but little confusion to frame this query.

To find out the sentence..
we need the starting point and end point of the sentence.
using SUBSTRING and CHARINDEX we can get that.

Table Name: Test
Column Name: Content

--to find out the index position of the searching text
select charindex('million',content,1) from test

Result:
-------
185

-- to find out the starting position of the sentence contains searching text

select len(content)- (charindex('.',reverse(content),charindex(reverse( 'million'), reverse(content),1))) from test

Result:
-------
157

-- to find out the end position of the sentence contains searching text
select charindex('.',content,charindex('million',content, 1)) from test

Result:
-------
258


so, now the searching sentence starting position is "157" and end position is "258"

now using substring we can get the sentence.

Is this correct way to proceed my requirement.


Cheers
Sankar

An alternative would be to use FULL TEXT SEARCH on the table, this gives you more options and better performance because the text content is indexed in the background. However you need to learn a new vocabulary and syntax to create the queries.

--

Joe (Microsoft MVP - XML)

Thanks for your suggestion.

Getting knowledge in that.

Cheers
Sankar

Yes, you COULD use SUBSTRING and CHARINDEX. But you are going to find that pretty unreliable. Not to mention clumsy to do in T-SQL.

Just for instance,
     Mr. Jones, the V.P. of Engineering, said we could produce 3 million P.C. components this year.

If you looked for "3 million" and then went backwards and forwards to the periods, your "sentence" would end up being
     of Engineering, said we could produce 3 million P.

Now, it's going to be tough to do better than that, even in C# code. So you might need to revise your definition of "sentence". But in any case, is there REALLY *any* reason to do this 100% in T-SQL???

yes,

using SUBSTRING AND CHARINDEX Im getting the same problem which has mentioned by Pedant.

Also I have used the FULL TEXT SEARCH concept.
But this is different from what Im expecting.

http://dnnblog.venexus.com/MS+SQL+Se...Info+Dump.aspx

Using FULL TEXT SEARCH we can search text with the nearest text and so on, using CONTAINS, FREETEXT.
It is also return the full content not the sentence.

I don't know whether my requirement is possible in SQL or not.



Cheers
Sankar

You haven't answered the question:

Is there really any reason to do this all in T-SQL code?

If you did it in C# code, you could put in some heuristics to help you better define a sentence.

Just for example, start with these rules:

(1) A sentence starts at the first character after the [PERIOD PLUS SPACE] (taken as a unit) that precedes the found text and ends at the next period [PERIOD PLUS SPACE], excepting that abbreviations will not be considered as sentence terminators.
(2) So many common abbreviations will be kept in a table and thus ignored in determining sentence limits. Examples include
    Mr. Mrs. etc. et. al. ibid. and more.
(3) Combined groups of letters and periods with no intervening spaces will be considered abbreviations.
    Thus, "V.P." and "P.C." will be treated as abbreviations

This is still not perfect. For example, if an abbreviation happens at the end of a sentence, then you won't *FIND* the end of the sentence.
    Staff members, etc. Mr. Jones is the V.P. Dr. Richards is CEO.
If you were looking for "Jones" and followed the above rules for abbreviations, *ALL* of that text would be considered to be a single sentence. So you can try introducing other rules that might help find sentences.

But this is the English language. It's *NOT* a computer language. Parsing will never be perfect. Can't be. You just have to do as good as you can.

Finally, what happens if the text you are searching for occurs multiple times in the big block of text??? Will you try to show all sentences containing it?

This is *NOT* an appropriate job for SQL. It's only marginally an appropriate job for software of any kind.