?
Solved

select the first paragraph of a text field using sql

Posted on 2006-05-02
11
Medium Priority
?
413 Views
Last Modified: 2010-07-27
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
            
            Next
            
            'replace any carriage returns in the string
            rtnStr = Replace(rtnStr, Chr(13), "</p><p>")
            
            'return the paragraphs to the function
            rtnNoOfParas = rtnStr

      'otherwise
      Else

            'there is no carriage returns - return the string (or only paragraph) to the function
            rtnNoOfParas = tmpStr

      End If
      
End Function

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!
0
Comment
Question by:hc0904pcd
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Expert Comment

by:peterxlane
ID: 16593009
I am not a SQL expert, so I don't know how efficient this is, but here is what I came up with:

SELECT    
   CASE PATINDEX('%' + CHAR(13) + '%', fieldname)
      WHEN 0 THEN fieldname
      ELSE SUBSTRING(fieldname, 1, PATINDEX('%' + CHAR(13) + '%', fieldname))
   END AS FirstParagraph
FROM        
YourTable
0
 
LVL 6

Author Comment

by:hc0904pcd
ID: 16593344
hi, thanks for the reply - wow, i had no idea you could use 'select cases' in sql statements? thats good to know.

i did some more digging on google and came up with a similar kinda thing:

SELECT LEFT(CAST(story AS VARCHAR), CHARINDEX(' ', CAST(story AS VARCHAR))) AS firstPara from tableName

this uses the 'CHARINDEX' function to find the numeric position of the space (the function uses CHARINDEX('what to look for', 'what to find it in')) - which works!

unfortunately, i cant find out how to search for the carriage return.... changing the statement to:

SELECT LEFT(CAST(story AS VARCHAR), CHARINDEX('" & Chr(13) & "', CAST(story AS VARCHAR))) AS firstPara from tableName

returns nothing...

what do you reckon?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16601345
SELECT LEFT(CAST(story AS VARCHAR), CHARINDEX('char(13)', CAST(story AS VARCHAR))) AS firstPara from tableName

with or without the single quotes, although im thinking without.  char() converts the ascii code to the character though.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:hc0904pcd
ID: 16601519
hi, thats what i thought - and it works for other characters but not char(13)!! but i found something else which is confusing:

for example, this is in the column being grabbed from the database:

-start-
How to find

carriage returns.
-stop-
(ignore the -start- and -stop-)

if i run this sql to find the position of the 'd' character in 'find':

SELECT CHARINDEX(char(100), CAST(story AS VARCHAR)) AS firstPara from tableName

it returns the number 11 which is correct.

the funny thing is, i change the sql to look for the 'c' in 'carriage':
SELECT CHARINDEX(char(99), CAST(story AS VARCHAR)) AS firstPara from tableName
it returns the number 16!

and if i change it to:

SELECT CHARINDEX(char(13), CAST(story AS VARCHAR)) AS firstPara from tableName

it returns 0.

so something is happening between the end of the word 'find' and 'carriage' (i.e. the carriage return) but i cant seem to pastern match it using 'char()'.

the only thing i can think of is that it's actually got to find a line end and a carriage return to return a value?

all very confusing - it seems like it should work, but something is missing...
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16601633
try looking for a character 10.  that is a line feed.  perhaps sql doesnt store the cr with the lf.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16602700
You need to determine what you mean by carriage return.  Is it:
CHAR(13)          -- Cr
CHAR(10)          -- Lf
CHAR(13) + CHAR(10) CrLf

This can easily ascertained by using a simple Hex text editor.

Once you have that then all you have to do is use the SUBSTRING function with CHARINDEX to get the paragraph.

>>SELECT LEFT(CAST(story AS VARCHAR), CHARINDEX(' ', CAST(story AS VARCHAR))) AS firstPara from tableName<<
Don't do this.  You have just converted your ntext column to a varchar(30) data type.  In case you don't believe me, use this script.

Declare @MyTable table (MyColumn ntext)

Insert @MyTable (MyColumn) VALUES (REPLICATE('A', 20000))

Select CAST(MyColumn as varchar), LEN(CAST(MyColumn as varchar))
From @MyTable
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16602712
Unfortunately, due to the nature of (n)text data types there is no easy way to get a number of paragraphs with a single Select statement.  You would have to query the column multiple times.  So unless you are up to using a Stored Procedure you may be better off using VBScript.
0
 
LVL 12

Accepted Solution

by:
peterxlane earned 2000 total points
ID: 16609603
What was the result of running the query that I suggested?  It worked perfectly for me...

SELECT    
   CASE PATINDEX('%' + CHAR(13) + '%', fieldname)
      WHEN 0 THEN fieldname
      ELSE SUBSTRING(fieldname, 1, PATINDEX('%' + CHAR(13) + '%', fieldname))
   END AS FirstParagraph
FROM        
YourTable
0
 
LVL 6

Author Comment

by:hc0904pcd
ID: 16611002
hi, thanks for all the replies - interesting stuff! the carriage return thing has always been fun...

i managed to get this sql to work:

SELECT CHARINDEX(char(10), CAST(story AS VARCHAR)) AS firstPara from tableName

but only on rows where i had manually typed in the carriage return, not when the data has come from a web form. i'm not sure why this is but as 'acperkins' points out, this may be to do with how the carriage return is defined (ie char(13), char(10) or a combination thereof). in addition, i agree - turning a ntext field into a varchar is probably not the best plan...

'peterxlane', i tried your script a 2nd time and it worked! cant work out why it didn't the 1st time but it may have been a spelling mistake on my part.

just to check (as i like to understand these things) does the script query the row to see if it can find a carriage return. if it doesn't, it just returns the row (which is the equivalent to the 1st paragraph) but if it does, it then uses a SUBSTRING query find the position of the carriage return and then returns the text before it?

the logic makes sense and i'm glad its working - i'm pretty sure this will be a faster way to return the 1st paragraph rather than loading all the text into the record return and then filtering it out? at 1st test, my pages seem to have speeded up so it's looking good!

thanks again for the replies.
0
 
LVL 6

Author Comment

by:hc0904pcd
ID: 16611008
also found a very good article on 'CHARINDEX' and 'PATINDEX' here:

http://www.databasejournal.com/features/mssql/article.php/3071531
worth having a look.
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16614544
Yes, that is exactly how the logic works.  I am taking a look at that article you mentioned.  I had actually never used PATINDEX until I wrote that query.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question