select the first paragraph of a text field using sql
Posted on 2006-05-02
i've been using a function to return a number of paragraphs from a ntext field in a sql server database:
Function rtnNoOfParas(strTxt, noOfParagraphs)
'grab the passed string
tmpStr = strTxt
'if the string isnt empty and has carriage returns in it, it has more than 1 parargraph
If (tmpStr <> "") and (inStr(tmpStr, Chr(13)) <> 0) Then
'load the string into an array and split at the carriage return
colArray = Split(tmpStr, Chr(13))
'loop through the array, returning the passed number of paragraphs
For iLoop = 0 to noOfParagraphs
rtnStr = colArray(iLoop) & rtnStr
'replace any carriage returns in the string
rtnStr = Replace(rtnStr, Chr(13), "</p><p>")
'return the paragraphs to the function
rtnNoOfParas = rtnStr
'there is no carriage returns - return the string (or only paragraph) to the function
rtnNoOfParas = tmpStr
although this works, i got to thinking wouldn't it be better to do this in the sql when you query the database? it seems unnecessary to load all of the text from the ntext field just to extract the 1st paragraph - in addition, i'm pretty sure that my pages are slowing down when the row contains a very long string of text (the whole text sting is being loaded into a variable and therefore taking up more memory?).
is it possible to do this, i.e. select the 1st paragraph in sql?
any suggestions would be great!