Re: LENGTH function and trailing spaces

> Subject: Re: LENGTH function and trailing spaces > Date: 12 Apr 1995 15:50:57 GMT > Reply-To: kbradley@130.35.1.6 (Kirk Bradley - Mainframe and Integration Technologies) > Organization: Oracle Corporation. Redwood Shores, CA > > Now we're getting somewhere. Can you give me an example then > of why you'd ever use LENGTH in SQL? Especially since the > function will not allow an expression? I can't think of > anything particularly useful that LENGTH can do for me. Okay. My company uses revision letters for our engineering drawings. After rev Z comes rev AA, and so on. Now in my database designs, I did it right. :-) Rev is right justified with leading spaces, so normal alpha sort works. Some other guys designed their database wrong, IMHO. They left justified their revs, so you need to have a two part sort, [order by length(rev), rev] to get the revs to come out in the right order. I've left out some of the other complicating details, but I think this illustrates the point. > p.s. One reason to havethe REAL length of columns (not > the stripped length) is to be able to calculate how much > space a row takes on disk (you have to add in the column, > row and block overheads of course) > -- > Kirk Bradley > Oracle Corporation > Mainframe and Integration Technologies Group This last reason you give only applies if you use varchars instead of chars. If you use (fixed length) chars, then the column systables.rowsize tells you exactly how long a row's data is. (I suppose it is the max size, if varchars are used.) Then you add in the overheads that occur at the page level, etc. Of course, you are used to Oracle, where in O6 and before all character stuff was stored more or less as varchar. Now in O7 you also have real fixed length char data. But I don't know where the rowsize equivalent is in the Oracle data dictionary. There's a lot of neat info in there, but it is darn hard to find because of the inconsistent naming. At least in Informix, all the data dictionary tables are systhis or systhat. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Lockheed Martin, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+