what is the difference between DATALENGTH() and LEN() other than the fact that DATALENGTH() works on text data types?
Bernd VanSkiver [EMAIL PROTECTED] ColdFusion Developer ----- Original Message ----- From: "Brad Comer" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, January 03, 2002 2:37 PM Subject: RE: Text Size > I just tested it and it worked for me! > > SELECT DATALENGTH(txttblfield) AS Expr1 > FROM tableZ > > 115 > 34 > 123 > > -----Original Message----- > From: Brad Comer [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 03, 2002 2:34 PM > To: SQL > Subject: RE: Text Size > > > HERE YOU GO FROM THE HELP SECTION WITHIN ENTERPRISE MANAGER FOR MSSQL 2000. > > I am sure you can get it with datalength() > > -------- excerpt -------- below ------------ > > TEXTPTR > Returns the text-pointer value that corresponds to a text, ntext, or image > column in varbinary format. The retrieved text pointer value can be used in > READTEXT, WRITETEXT, and UPDATETEXT statements. > > Syntax > TEXTPTR ( column ) > > Arguments > column > > Is the text, ntext, or image column to be used. > > Return Types > varbinary > > Remarks > In Microsoft SQL Server� 2000, for tables with in row text, TEXTPTR returns > a handle for the text to be processed. You can obtain a valid text pointer > even if the text value is null. > > If the table does not have in row text, and if a text, ntext, or image > column has not been initialized by an UPDATETEXT statement, TEXTPTR returns > a null pointer. > > Use TEXTVALID to check whether a text pointer exists. You cannot use > UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer. > > These functions and statements are also useful with text, ntext, and image > data. > > Function or statement Description > PATINDEX('%pattern%', expression) Returns the character position of a given > character string in text or ntext columns. > DATALENGTH(expression) Returns the length of data in text, ntext, and image > columns. > SET TEXTSIZE Returns the limit, in bytes, of the text, ntext, or image data > to be returned with a SELECT statement. > SUBSTRING(text_column, start, length) Returns a varchar string specified by > the given start offset and length. The length should be less than 8 KB. > > > Examples > A. Use TEXTPTR > This example uses the TEXTPTR function to locate the image column logo > associated with New Moon Books in the pub_info table of the pubs database. > The text pointer is put into a local variable @ptrval. > > USE pubs > GO > DECLARE @ptrval varbinary(16) > SELECT @ptrval = TEXTPTR(logo) > FROM pub_info pr, publishers p > WHERE p.pub_id = pr.pub_id > AND p.pub_name = 'New Moon Books' > GO > > B. Use TEXTPTR with in row text > In SQL Server 2000, the in row text pointer must be used inside a > transaction. Here is an example. > > CREATE TABLE t1 (c1 int, c2 text) > EXEC sp_tableoption 't1', 'text in row', 'on' > INSERT t1 VALUES ('1', 'This is text.') > GO > BEGIN TRAN > DECLARE @ptrval VARBINARY(16) > SELECT @ptrval = TEXTPTR(c2) > FROM t1 > WHERE c1 = 1 > READTEXT t1.c2 @ptrval 0 1 > COMMIT > > C. Return text data > This example selects the pub_id column and the 16-byte text pointer of the > pr_info column from the pub_info table. > > USE pubs > GO > SELECT pub_id, TEXTPTR(pr_info) > FROM pub_info > ORDER BY pub_id > GO > > Here is the result set: > > pub_id > ------ ---------------------------------- > 0736 0x6c0000000000feffb801000001000100 > 0877 0x6d0000000000feffb801000001000300 > 1389 0x6e0000000000feffb801000001000500 > 1622 0x700000000000feffb801000001000900 > 1756 0x710000000000feffb801000001000b00 > 9901 0x720000000000feffb801000001000d00 > 9952 0x6f0000000000feffb801000001000700 > 9999 0x730000000000feffb801000001000f00 > > (8 row(s) affected) > > This example shows how to return the first 8,000 bytes of text without using > TEXTPTR. > > USE pubs > GO > SET TEXTSIZE 8000 > SELECT pub_id, pr_info > FROM pub_info > ORDER BY pub_id > GO > > Here is the result set: > > pub_id pr_info > ------ ----------------------------------------------------------------- > 0736 New Moon Books (NMB) has just released another top ten publication. > With the latest publication this makes NMB the hottest new publisher of the > year! > 0877 This is sample text data for Binnet & Hardley, publisher 0877 in the > pubs database. Binnet & Hardley is located in Washington, D.C. > > This is sample text data for Binnet & Hardley, publisher 0877 in the pubs > database. Binnet & Hardley is located in Washi > 1389 This is sample text data for Algodata Infosystems, publisher 1389 in > the pubs database. Algodata Infosystems is located in Berkeley, California. > > 9999 This is sample text data for Lucerne Publishing, publisher 9999 in > the pubs database. Lucerne publishing is located in Paris, France. > > This is sample text data for Lucerne Publishing, publisher 9999 in the pubs > database. Lucerne publishing is located in > > (8 row(s) affected) > > D. Return specific text data > This example locates the text column (pr_info) associated with pub_id 0736 > in the pub_info table of the pubs database. It first declares the local > variable @val. The text pointer (a long binary string) is then put into @val > and supplied as a parameter to the READTEXT statement, which returns 10 > bytes starting at the fifth byte (offset of 4). > > USE pubs > GO > DECLARE @val varbinary(16) > SELECT @val = TEXTPTR(pr_info) > FROM pub_info > WHERE pub_id = '0736' > READTEXT pub_info.pr_info @val 4 10 > GO > > Here is the result set: > > (1 row(s) affected) > > pr_info > ------------------------------------------------------------------------ > is sample > > > See Also > > DATALENGTH > > PATINDEX > > READTEXT > > SET TEXTSIZE > > Text and Image Functions > > UPDATETEXT > > WRITETEXT > > > -----Original Message----- > From: Bernd VanSkiver [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 03, 2002 2:27 PM > To: SQL > Subject: Re: Text Size > > > I need a way to do it in SQL. > > Bernd VanSkiver > [EMAIL PROTECTED] > ColdFusion Developer > ----- Original Message ----- > From: "Bryan Stevenson" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Thursday, January 03, 2002 2:25 PM > Subject: Re: Text Size > > > > ahhhh...no I use it on the CF side > > > > Bryan Stevenson > > VP & Director of E-Commerce Development > > Electric Edge Systems Group Inc. > > p. 250.920.8830 > > e. [EMAIL PROTECTED] > > --------------------------------------------------------- > > Allaire Alliance Partner > > www.allaire.com > > > > ----- Original Message ----- > > From: "Bernd VanSkiver" <[EMAIL PROTECTED]> > > To: "SQL" <[EMAIL PROTECTED]> > > Sent: Thursday, January 03, 2002 12:17 PM > > Subject: Re: Text Size > > > > > > > Were you using that function in SQL or something else though? > > > > > > Bernd VanSkiver > > > [EMAIL PROTECTED] > > > ColdFusion Developer > > > ----- Original Message ----- > > > From: "Bryan Stevenson" <[EMAIL PROTECTED]> > > > To: "SQL" <[EMAIL PROTECTED]> > > > Sent: Thursday, January 03, 2002 1:50 PM > > > Subject: Re: Text Size > > > > > > > > > > Are you sure Len() doesn't work on the "text" datatype? I'm pretty > sure > > I > > > > use it to only show the first "x" characters of text fields followed > by > > > > "...click for more". > > > > > > > > Bryan Stevenson > > > > VP & Director of E-Commerce Development > > > > Electric Edge Systems Group Inc. > > > > p. 250.920.8830 > > > > e. [EMAIL PROTECTED] > > > > --------------------------------------------------------- > > > > Allaire Alliance Partner > > > > www.allaire.com > > > > > > > > ----- Original Message ----- > > > > From: "Bernd VanSkiver" <[EMAIL PROTECTED]> > > > > To: "SQL" <[EMAIL PROTECTED]> > > > > Sent: Thursday, January 03, 2002 11:40 AM > > > > Subject: Text Size > > > > > > > > > > > > > I am using Microsoft SQL 2000. Is there a way to see how many > > > > > bytes/characters a specific field is taking up that is datatype > text? > > I > > > > use > > > > > the LEN() function on varchar and char datatypes to get then length, > > but > > > > > that function doesn't work on text datatype fields. > > > > > > > > > > Bernd VanSkiver > > > > > [EMAIL PROTECTED] > > > > > ColdFusion Developer > > > > > > > > > > > > > > > > > ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source Step by Step ColdFusion http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
