Re: LENGTH function and trailing spaces

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