Select only first four lines, from Sql text field

Question

Sql Server 2008 Express >> Visual Web Developer >> C#

I'm pulling records out of the table like this:

SELECT Name, Category, Review FROM ReviewTable

This works fine but the Review field Type in SQL Server is text and is very long (think a magazine article).

I only want to pull the first four lines from the Review field for each row, and display them in my repeater control. These lines will be like a teaser of the article.

Is this possible? How can it be accomplished?

Answer 1

This will return this first 1000 characters from the review.

SELECT Name, Category, CAST(Review AS VARCHAR(1000) FROM ReviewTable

If you must have the first 4 lines you need to use some split function. This could work:

CREATE FUNCTION [dbo].[Split]
(
    @SearchString VARCHAR(8000),
    @Separator VARCHAR(5),
    @MaxItems INT
)
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS

BEGIN
DECLARE @tmpStr VARCHAR(8000), @intSeparatorLength INT, @counter int

IF @MaxItems IS NULL
    SET @MaxItems = 2147483647 -- max int

SET @intSeparatorLength = LEN(@Separator)
SET @Counter = 0

SET @tmpStr = @SearchString
    WHILE 1=1 BEGIN
     INSERT INTO @strtable VALUES ( SUBSTRING(@tmpStr, 0 ,CHARINDEX(@Separator, @tmpStr)))
     SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@Separator,@tmpStr)+LEN(@Separator),8000)
     SET @counter = @counter + 1  
     IF (CHARINDEX(@Separator,@tmpStr) < 1 OR @counter >= @MaxItems)
      BREAK
    END

RETURN
END

Usage: select * from dbo.split('aaa**bbbb**CCCC**dddd**eeee**dffff**ggggg', '**', 4)

Answer 2

Well ,the first for lines may be a bit more difficult, but why don't you just put out the first 250 characters or so?

SELECT Name, Category, SubString(Review, 1, 250) AS Review FROM ReviewTable

Answer 3

If your database server is in the same local network as your web server, I think I'd probably select the entire field, since you're accessing it at all. You'll still have to do a lookup to access any data in that field, so sql performance-wise for finding the data is a non-issue. The only downside of retrieving the entire field would be the amount of data passed between the servers. Thus: if they're in the same network, I'd say this would definitely be cheaper than tampering with each record during selection. It also gives you the ability to cache your response, so that you don't have to hit the database again when the user wants to see the full version of the text.

But, to answer your question, the below should probably do it, altho it looks rather tacky

SELECT Name, Category, left(convert(varchar(8000), Review), charindex('\n', convert(varchar(8000), Review), charindex('\n', convert(varchar(8000), Review), charindex('\n', convert(varchar(8000), Review), charindex('\n', convert(varchar(8000), Review))+1)+1)+1)-1) FROM ReviewTable

...hrrm, yeah, really, i'd consider my first paragraph