Did you try: length() BTC
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 03, 2002 2:44 PM To: SQL Subject: RE: RE: Text Size Anybody know how to do the same thing in mySQL or Oracle? Travis ---- Original Message ---- From: Brad Comer <[EMAIL PROTECTED]> Sent: 2002-01-03 To: SQL <[EMAIL PROTECTED]> 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 > > > > > > > > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
