Re: LENGTH function and trailing spaces

Sheesh.. THEIR not THERE.. Kirk Bradley - Mainframe and Integration Technologies (kbradley@130.35.1.6) wrote: : Thanks.. that's an ok example (guess I would've just written a function : to right justify THERE revision letters so that there was a unified : look to both tables but..) and I appreciate the feedback. : I, of course, have an Oracle bias but was truly curious about the : use of the length builtin. It seemed more oriented to 4-GL than : to SQL and I was trying to make sure I understood it. It sure sounds : like it's very rare to use LENGTH() in a SQL statement and that : was my main interest. : Much obliged. : Alan Popiel (alan@po.den.mmc.com) wrote: : : > 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. | : : +---------------------------+-----------------------------------------------+ : -- : Kirk Bradley : Oracle Corporation : Mainframe and Integration Technologies Group -- Kirk Bradley Oracle Corporation Mainframe and Integration Technologies Group