Help Needed with filtering query results

How can I do this...

Pretend that this is my SQL statement...

SELECT pageData FROM dataTable WHERE pageData LIKE 'winners'

Pretend that the text below is what gets returned.

"Congratulations to the 51 state-level winners who will take part in
the National Cookoff of the National Chicken Cooking Contest."


I want to be able to start to display the recordset based upon the term
that I was searching for and then say, 25 more characters so that the
display to the screen looks like...

winners who will... 'get the point?
I'm lost here. My head is smoking.

Thanks

Re: Help Needed with filtering query results

If "winners" appears 100 chars into the text do want to display from the
beginning to [winners+25], or from [winners-25] to [winners+25] ?

Use instr (vbscript) to search for the first occurence of the term, then use
left() to get the first part + 25.

You should also try to make sure you end with a complete word rather than
cutting in the middle of something. Search for the next space and include
the string up to that point.

Tim.



wrote in message
news:1106949385.390541.45370@z14g2000cwz.googlegroups.com...
> How can I do this...
>
> Pretend that this is my SQL statement...
>
> SELECT pageData FROM dataTable WHERE pageData LIKE 'winners'
>
> Pretend that the text below is what gets returned.
>
> "Congratulations to the 51 state-level winners who will take part in
> the National Cookoff of the National Chicken Cooking Contest."
>
>
> I want to be able to start to display the recordset based upon the term
> that I was searching for and then say, 25 more characters so that the
> display to the screen looks like...
>
> winners who will... 'get the point?
> I'm lost here. My head is smoking.
>
> Thanks
>

Re: Help Needed with filtering query results

On 28 Jan 2005 13:56:25 -0800, joew@vca.com wrote:

>How can I do this...
>
>Pretend that this is my SQL statement...
>
>SELECT pageData FROM dataTable WHERE pageData LIKE 'winners'
>
>Pretend that the text below is what gets returned.
>
>"Congratulations to the 51 state-level winners who will take part in
>the National Cookoff of the National Chicken Cooking Contest."
>
>
>I want to be able to start to display the recordset based upon the term
>that I was searching for and then say, 25 more characters so that the
>display to the screen looks like...
>
>winners who will... 'get the point?
>I'm lost here. My head is smoking.

Several methods come to mind, maybe the easiest is to use the Split
function, or more elegant with a RegExp.

Split:

http://www.devguru.com/Technologies/vbscript/quickref/split. html

SplitDelimiter = " winners "
SplitString = "Congratulations to the 51 state-level winners who will
take part in the National Cookoff of the National Chicken Cooking
Contest."

' Split the string into parts
SplitArray = Split(splitString,SplitDelimiter)

' Read 25 characters of second array element
SpliResult = "winners " & Left(SplitArray(1),25) & ...

Naturally you'd need to test the length in case it's less than 25
characters until the end of the string and so on.

RegExp:

http://www.devguru.com/Technologies/vbscript/quickref/regexp .html

strInput = ""Congratulations to the 51 state-level winners who will
take part in the National Cookoff of the National Chicken Cooking
Contest."

Set objRegExp = New RegExp
With objRegExp
.Pattern = "winner........................."
.Global = False
.IgnoreCase = True
End With

strOutput = objRegExp.Execute(strInput)

Okay, that RegExp is pretty lame since you probably want to match on
word boundaries up to the end of a string, but you can work out the
pattern with a few references from Google.

Jeff

Re: Help Needed with filtering query results

Thanks for the Set objRegExp = New RegExp info. I should say that I am
trying to search through a memo field in my db and then display about
25 words after my recordset is returned.

I am still stumbling.

- say that my record set is rs("pageData")
- say that the contents of my rs is below.

rs("pageData") = "Congratulations to the 51 state-level winners who" _
& " will take part in the National Cookoff of the" _
& " National Chicken Cooking Contest."

If I am searchin for 'winner'
-> If my request.form("whatIamSearchingFor") = "winner"

How do I return
'winnners who will take part...'

to the browser?

Thanks

Re: Help Needed with filtering query results

I used the code from the link that jeff gave me below, but I would I
incorporate your idea of returing the strKW plus the next 10
characters?

If you can clear this up for me I think I'm good to go.
Thanks

<%
strInput = "Congratulations to the 51 state-level winners who will
take" _
& " part in the National Cookoff of the National Chicken Cooking
Contest."

strKW = "winner"

'this sub finds the matches
Sub RegExpTest(strMatchPattern, strPhrase)
'create variables
Dim objRegEx, Match, Matches, StrReturnStr
'create instance of RegExp object
Set objRegEx = New RegExp

'find all matches
objRegEx.Global = True
'set case insensitive
objRegEx.IgnoreCase = True
'set the pattern
objRegEx.Pattern = strMatchPattern

'create the collection of matches
Set Matches = objRegEx.Execute(strPhrase)

'print out all matches
For Each Match in Matches

strReturnStr = Match.value
'print
Response.Write(strReturnStr & "
")
Next
End Sub

'call the subroutine
RegExpTest strKW, strInput
%>

Re: Help Needed with filtering query results

<<
Thanks for the Set objRegExp = New RegExp info. I should say that I am
trying to search through a memo field in my db and then display about
25 words after my recordset is returned.

I am still stumbling.

- say that my record set is rs("pageData")
- say that the contents of my rs is below.

rs("pageData") = "Congratulations to the 51 state-level winners who" _
& " will take part in the National Cookoff of the" _
& " National Chicken Cooking Contest."

If I am searchin for 'winner'
-> If my request.form("whatIamSearchingFor") = "winner"

How do I return
'winnners who will take part...'

to the browser?

Thanks
>>

Here is a function that has worked for me:

Function jpsvbParagraphAbbr(pvarFld, pintLen)
' Purpose: Paragraph abbreviate.
' Author: JPS, 1/2002.
' Remarks: Doesn't chop up the final word. Usually puts ... after.
' Example: pintLen of 15, Can't stop now. -> Can't stop...

' Dim var.
Dim varFld
Dim strChar
Dim intFinalWhiteSpaceBlockStartPos
Dim intFinalWhiteSpaceBlockEndPos
Dim I

' Set var.
varFld = pvarFld

' Quick exit if blank.
If IsNull(varFld) Or (Len(varFld) = 0) Then
jpsvbParagraphAbbr = ""
Exit Function
End If

' Convert to string.
varFld = CStr(varFld)

' Quick exit if not too long.
If Len(varFld) <= pintLen Then
jpsvbParagraphAbbr = varFld
Exit Function
End If

' Truncate to 1 more char than the needed len.
varFld = Left(varFld, pintLen + 1)

' Init.
intFinalWhiteSpaceBlockStartPos = 0

' Go back char by char to find the final white space block start pos.
' (There could be several white spaces in a row.)
' (I.e. if desired len is 15, find pos of the 1st space after the p in
"Can't stop now.")
For I = pintLen + 1 To 1 Step -1
' Set var.
strChar = Mid(varFld, I, 1)

' If space, tab, CR, or LF.
If (strChar = " ") Or (strChar = Chr(9)) Or (strChar = Chr(13)) Or
(strChar = Chr(10)) Then
intFinalWhiteSpaceBlockStartPos = I
Else
If intFinalWhiteSpaceBlockStartPos > 0 Then
Exit For
End If
End If
Next ' Next I.

' Keep the proper num of chars from the left.
If intFinalWhiteSpaceBlockStartPos > 0 Then
' Truncate to just before final white space.
varFld = Left(varFld, intFinalWhiteSpaceBlockStartPos - 1)
Else
' Keep the orig up to the desired len (i.e. drop the temp addtl char).
varFld = Left(varFld, pintLen)
End If

' Add ... at end.
' (If varFld already ends in ., then only add two more .'s)
If Right(varFld, 1) = "." Then
varFld = varFld & ".."
Else
varFld = varFld & "..."
End If

' Return val.
jpsvbParagraphAbbr = varFld
End Function

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!