Hello All,

I have this table that has 3 fields,

1. PostID
2. LineText
3. LineNumber

So for some sample data we would have

PostID LineText LineNumber
100 Hello World. 1
100 I am doing fine 2
100 And yourself? 3
100 Good to hear! 4

Well what i need is a stored procedure that will simply
return me 1 row representing the LineText for PostID 100.

So it would return me

Hello World. I am doing fine And Yourself? Good to Hear!

as one field instead of 4 different fields.

Sounds simple, but lets take a post that has greater than 8000
characters i can't simply do

@LineText = @LineText + Articles.LineText because the definition of the variable @LineText(8000) has a max limit of 8000 characters and well i will have posts with greater than that number!!! So my question is how can i do string concatination of a variable of type
text or nText in a stored procedure?

if i do the following it gives me an error:

Declare @LineText nText

@LineText = @LineText + ' Hello World '

it complains that you cannot use the operator Plus equals!

all i want to do is some simple string concatination in a stored procedure with a variable length!

thanks

ntext, text, and image type are not allow as local variables

can you post your full statement?

CREATE FUNCTION dbo.fnconcatenate
( @employeeID int)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @LineText ntext

SELECT @lineText = COALESCE(@linetext + ',','') + LastName + ' ' + firstName
FROM dbo.employees a with (NOLOCK)
WHERE employeeID = @employeeID

RETURN '(' + @linetext + ')'
END
GO

SELECT LastName + dbo.fnconcatenate(employeeID)
FROM dbo.employees p with (nolock)

Thanks for your reply:

yea it keeps yelling at me that i cannot declare ntext, text, image as variables. Then how am i supposed
to build a string in my stored procedure that is over 8000 characters long?!!

essentially this is what i thought i could do.

Create Function sp_GetPostBody(@PostID int)
Returns nText
as
BEGIN
    Declare @LineText as nText
    Declare @Temp as varchar(255)

    Declare LineTextCursor Cursor for
        Select LineText FROM tb_PostBody WHERE PostID=@PostID
        for Read Only;

        Open LineTextCursor

        Fetch Next from LineTextCursor
            INTO @Temp

        While @@FETCH_STATUS = 0
        BEGIN
            Set @LineText = @LineText + @Temp

            Fetch Next from LineTextCursor
            INTO @Temp
        END

        CLOSE LineTextCursor
        DEALLOCATE LineTextCursor

    Return @LineText
End

The way we do our posts is that we break up a whole post into 255 character segments such that it is easier for us to search
text which is a whole other discussion, but essentially i need something to return me the article as a whole becase
in the db it is inserted as 255 character segments.

If there is a clever sp that can return me the whole article that would be great, but as for now all i can think of is
creating a cursor and looping through a recordset and concatinating the string then returning the resulting set.

Any thoughts?

Have you looked information on fulltext searching?

no i have not, would you care to take some time to enlighten us? or me whatever! haha

I may not be able to explain it better than books online but you mention that there was need to search text documents without have to break them up into separate segments. Using ntext, text or image type data fulltext searching enabled can help with querying those data type. Please look it up in Books online. Here is a small insert

Filtering Supported File Types
When a cell in an image column contains one of certain types of documents, full-text search uses a filter to interpret the binary data. The filter extracts the textual information from the document and submits it for indexing and subsequent querying.

Microsoft® SQL Server™ 2000 includes filters for these file extensions: .doc, .xls, .ppt, .txt, and .htm.

Many document types can be stored in a single image column. For each document, SQL Server chooses the correct filter based on the file extension. Because the file extension is not visible when the file is stored in an image column, the file extension must be stored in a separate column on the table. This type column can be of any character-based data type and contains the document file extension, such as .doc for a Microsoft Word document. If the type column is NULL, the document is assumed to be a text file (.txt).



Note For full-text indexing, a document must be less than 16 megabytes (MB) in size and must not contain more than 256 kilobytes (KB) of filtered text.


The document-type column is created in these ways:

In the Full-Text Indexing Wizard, select the image column for indexing, and then specify a Binding column to hold the document type.


The sp_fulltext_column stored procedure accepts an argument for the column to contain the document types.
To view the document type, use the sp_help_fulltext_columns stored procedure to return the column name and column ID.

After the image column is indexed, it can be queried using the search predicates CONTAINS and FREETEXT.



Note A filter may be able to handle objects embedded in the parent object, depending on its implementation. SQL Server does not configure filters to follow links to other objects.


You can create custom filters for full-text indexing of additional file types. For more information about creating custom filters, search on "custom filters" in the Platform SDK section of the MSDN® Library at Microsoft Web site.

Hi JR,
Did u got the solution to the problem you were facing. I am facing the similar kind of the problem. Please let me know.

Regards
rahul pokharna


regards
rahul pokharna