Re: LENGTH() and VARCHAR

If you would have read my previous posts you would see that I did say that length() does not count trailing spaces. I have no problem with documented behavior. My question is: is there a way to get the length of a column, in a SQL statement, that includes the trailing spaces. It appears there is not and that's fine and dandy. One suggestion was to take the length of the column of interest concatenated with a single character and then subtract one e.g. length(varcharcolumn+'.')-1 which, I think, has several problems: 1) the result of the concatenation may produce an intermediate result that exceeds the size of a varchar. I agree that it's rare but it's possible 2) in the same v5.0 doc the specification for LENGTH() in a SQL stmt (not 4-gl etc.) says that only column names and a few other things are allowed. It doesn't say you're allowed to use an expression. Maybe the doc is wrong (I will try it when I get the chance) I'm not trying to bash Informix. I'm just looking for reasonable mappings between Oracle and Informix. Sometimes, perhaps a lot of the time, there won't be any. That's fine. Perhaps a solution is for me to write my own procedure called oracle_length and have it return things the way an Oracle user would expect. Anybody have any idea what the overhead of a user defined function is? Charles Francis (ancrcs4!ancrcs4!cfrancis@ancrcs3.attmail.com) wrote: : >Right.. it gives their DEFINED lengths not the actual : >lengths (for VARCHAR at least) as stored for a given : >row. It sounds like nobody that uses Informix (at least : >on this forum ;-)) uses length() for this purpose and : >so the fact that you can't do this: : > INSERT INTO VT(VARCOLUMN) VALUES ('Kirk ') : > SELECT * FROM VT WHERE LENGTH(VARCOLUMN) = 7 : >and have the SELECT return that row. : >Correct? : >p.s. I'm getting this info from the 5.0 SQL ref that says : >that TRAILING SPACES ARE STORED in VARCHAR columns. If : >that isn't true then all bets are off I guess. : -- : >Kirk Bradley : >Oracle Corporation : >Mainframe and Integration Technologies Group : Kirk : If you would read a little more you would find that the LENGTH() function : returns the number of bytes in a string after removing trailing spaces. : Also I would not expect the select you give as an example to "return that : row" you inserted. I would expect that many rows might be selected. : Further more if Informix did everything the way Oracle does, there would : not be a need for Oracle. Assuming there is a need for Oracle. : If you don+t like something don+t use it. : Chuck Francis -- Kirk Bradley Oracle Corporation Mainframe and Integration Technologies Group