Post reply

Retrieving first 100 chars from a column of type text

  • September 27, 2004 at 6:00 pm

    #59955

    Hi,

    I have a column, data type Text in one of the tables. Is that possible to retrieve only the first 100 characters from that column?

    TAI.

  • September 27, 2004 at 9:45 pm

    #524581

    Yes, just use

    select substring([textfield],1,100)

    Regards

    Phil

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 28, 2004 at 6:15 am

    #524610

    Or left(field, 100)

    Brian

  • September 28, 2004 at 6:33 am

    #524612

    cannot use LEFT with text data type

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 28, 2004 at 7:36 am

    #524628

    This example from books on line seems to give the answer?
    Examples

    This example reads the second through twenty-sixth characters of the pr_info column in the pub_info table.

    USE pubsGODECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(pr_info)    FROM pub_info pr INNER JOIN publishers p      ON pr.pub_id = p.pub_id       AND p.pub_name = 'New Moon Books'READTEXT pub_info.pr_info @ptrval 1 25GO

  • September 28, 2004 at 7:39 am

    #524629

    Sorry about the formatting earlier, you need to see the READTEXT explanantion from BOL

    USE pubs

    GO

    DECLARE @ptrval varbinary(16)

    SELECT @ptrval = TEXTPTR(pr_info)

       FROM pub_info pr INNER JOIN publishers p

          ON pr.pub_id = p.pub_id

          AND p.pub_name = 'New Moon Books'

    READTEXT pub_info.pr_info @ptrval 1 25

    GO

     

  • September 28, 2004 at 12:26 pm

    #524686

    Just remember that first 100 char should be expressed as:

    READTEXT pub_info.pr_info @ptrval 0 100

    where 0 is the first char and 100 is the length...

    HTH

     

  • September 28, 2004 at 2:42 pm

    #524703

    Thanks for your reply.

  • September 29, 2004 at 2:22 am

    #524745

    You could also cast the Text as Varchar, andreplace CR/LF by a space.

    I use:

    LTRIM(REPLACE(CAST([TextField] AS VARCHAR(100), CHAR(13) + CHAR(10), ' ')))

    Sometimes it ends on a #13, in my solution this it not a problem, but you could raplace this single #13 by nothing.

    I even have a ... on the end when the length is more then 100.

    I use a (pseudo code):

    +CASE WHEN LEN > 100 THEN '...' ELSE '' END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply