Re: SELECT CAST(123 AS char) -> 1

Dean Gibson (DB Administrator) wrote:
 > On 2008-02-12 07:30, Ken Johanson wrote:
 >>>
 >>> Sure, but you're a prime candidate for understanding the value of
 >>> following the spec if you're trying to write software that works with
 >>> multiple databases.
 >>
 >> The spec has diminished in this (CAST without length) context:
 >> a) following it produces an output which has no usefulness whatsoever
 >> (123 != 1)
 > I *OFTEN* use a cast of CHAR to get just the first character.
 >

I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make the code
more readable for other data types..

 >> b) all the other databases chose to not follow the spec in the context
 >> of cast and char with implicit length.
 >
 > I doubt that:
 >
 >
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm
 > http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx
 >

The actual behavior is to autosize on MS and My. I do not have DB2 but
would be curious to know how it behaves.

 >
 >>
 >> When the length is unqualified, a cast to char should one of:
 >>
 >> 1) failfast
 >> 2) auto-size to char-count (de facto)
 >> 3) pad to the max-length
 >
 > What is wrong with using VARCHAR for your
 > purpose????????????????????????????

Simply that a commonly used database (my) does not support it. I HAVE to
support that one (too widely in use), better/worse, its not my choice.
Even at the expense of its standards deviation, and for doubting the
vendor(s) will the change behavior (and break the app).