Concatenating two numbers as a string

[Bogdan Calmac <bc...@gmail.com>]

To concatenate two numbers in a string you have to do something like this (in
derby 10.3):

trim(char(min_value)) || trim(char(max_value))

This is pretty ugly, considering that it could have been as simple as:

min_value || max_value

Also, the documentation for the concatenate operator states that "Because
all built-in data types are implicitly converted to strings, this function
can act on all built-in data types". Is the documentation wrong?

While trying different things, I noticed that numeric types cannot be cast
to VARCHAR (which is also specified in the documentation). Is there some
technical limitation to prevent such a natural conversion?
-- 
View this message in context: http://www.nabble.com/Concatenating-two-numbers-as-a-string-tp17036203p17036203.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Concatenating two numbers as a string

[Rick Hillegas <Ri...@Sun.COM>]

Bogdan Calmac wrote:
> To concatenate two numbers in a string you have to do something like this (in
> derby 10.3):
>
> trim(char(min_value)) || trim(char(max_value))
>
> This is pretty ugly, considering that it could have been as simple as:
>
> min_value || max_value
>
> Also, the documentation for the concatenate operator states that "Because
> all built-in data types are implicitly converted to strings, this function
> can act on all built-in data types". Is the documentation wrong?
>
> While trying different things, I noticed that numeric types cannot be cast
> to VARCHAR (which is also specified in the documentation). Is there some
> technical limitation to prevent such a natural conversion?
>   
As a workaround, you can write your own casting function. Please see the 
CREATE FUNCTION section of the Derby Reference Manual. E.g.,

  select castIntToVarchar( a ) from t

Hope this helps,
-Rick

Re: Concatenating two numbers as a string

[Rick Hillegas <Ri...@Sun.COM>]

Bogdan Calmac wrote:
> To concatenate two numbers in a string you have to do something like this (in
> derby 10.3):
>
> trim(char(min_value)) || trim(char(max_value))
>
> This is pretty ugly, considering that it could have been as simple as:
>
> min_value || max_value
>
> Also, the documentation for the concatenate operator states that "Because
> all built-in data types are implicitly converted to strings, this function
> can act on all built-in data types". Is the documentation wrong?
>
> While trying different things, I noticed that numeric types cannot be cast
> to VARCHAR (which is also specified in the documentation). Is there some
> technical limitation to prevent such a natural conversion?
>   
Hi Bogdan,

I agree that Derby should support this cast. It is legal in the SQL 2003 
standard. Derby claims to be supporting the SQL 92 standard behavior. I 
have logged DERBY-3662 to track this issue.

Thanks for bringing this up,
-Rick