Professional Web Applications Themes

truncate ntext field using LEFT - Microsoft SQL / MS SQL Server

You can use the CONVERT statement to cast the TEXT variable into a VARCHAR variable (with some truncation if you exceed 8000 bytes) and the use LEFT on that variable. For example: CREATE TABLE samp_test ( colA TEXT ) GO INSERT INTO samp_test VALUES ('0123456789012345678901234567890123456789') SELECT LEFT(CONVERT(VARCHAR(10), colA), 5) FROM samp_test -- HTH, SriSamp Please reply to the whole group only! http://www32.brinkster.com/srisamp "Steven Allen (dybrn)" <com> wrote in message news:%phx.gbl...  there ...

  1. #1

    Default Re: truncate ntext field using LEFT

    You can use the CONVERT statement to cast the TEXT variable into a VARCHAR
    variable (with some truncation if you exceed 8000 bytes) and the use LEFT on
    that variable. For example:

    CREATE TABLE samp_test
    (
    colA TEXT
    )
    GO
    INSERT INTO samp_test VALUES ('0123456789012345678901234567890123456789')
    SELECT LEFT(CONVERT(VARCHAR(10), colA), 5) FROM samp_test
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Steven Allen (dybrn)" <com> wrote in message
    news:%phx.gbl... 
    there 


    SriSamp Guest

  2. #2

    Default Re: truncate ntext field using LEFT

    thank you this works, but so does another solution provided by
    Amy she said to try SUBSTRING(Details,1, 25) AS Details

    is there a difference or limit to one of these solutions?
    I tried to comapare both solutions thru the query yzer, but can not see
    any performance difference.

    Comments ?

    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 
    on [/ref]
    25) [/ref]
    truncate 
    > there 
    >
    >[/ref]


    Steven Guest

  3. #3

    Default Re: truncate ntext field using LEFT

    Try using SUBSTRING:

    SELECT TOP 5 EventID, EventDate, EnteredBy, Subject,
    SUBSTRING(Details,1, 25) AS Details
    ..................

    HTH


    "Steven Allen (dybrn)" <com> wrote in message
    news:%phx.gbl... 
    there 


    Amy Guest

Similar Threads

  1. Retrieve ntext field using ODBC Socket
    By syseng in forum Coldfusion Database Access
    Replies: 7
    Last Post: January 1st, 01:31 PM
  2. Lenght of NText field
    By PHRED-SE in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 11th, 10:56 PM
  3. ntext field not showing up (ASP)
    By tony in forum ASP Database
    Replies: 1
    Last Post: May 27th, 06:51 PM
  4. Updating part of the text in an NTEXT field
    By Rocky in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 10th, 02:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139