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).