Why does the statement:

1) select 'Day '+cast(22.0 as VarChar);

display: "Day " and not "Day 22"?

I thought the default varchar expression length was 10 characters.

Instead I need to use

2) select 'Day '+cast(22.0 as VarChar(2));

or

3) select Cast('Day '+cast(22.0 as VarChar) as VarChar);

to return "Day 22".

Why does #1 not work as expected?
TIA

Barry
v2.13B2

Barry wrote:

> 1) select 'Day '+cast(22.0 as VarChar);


I can't answer the question, but I went to check that "CAST" is what
you really need to convert a float to a string, and it is indeed. SQL
is funny!

--

Matthew Jones

Matthew


>I can't answer the question, but I went to check that "CAST" is what
>you really need to convert a float to a string, and it is indeed. SQL
>is funny!

That is not the word i was using yesterday, but its a lot politer

Roy

>>Barry wrote:
>>Why does the statement:
>>1) select 'Day '+cast(22.0 as VarChar);
>>not work as expected?

I am only guessing, but I would say 22.0 is a FLOAT, and this (in SQL terms) would be represented in memory as a number of bytes & a large number of decimal places, so you have to specify the size of the varchar to avoid it returning:

Day 22.00000000000000000000000000

therefore the default is to do nothing, rather than return the confusing string of 0000000s.

On 8/13/2014 12:55 PM, Barry wrote:
> 1) select 'Day '+cast(22.0 as VarChar);

You should never use varchar without length.

> display: "Day " and not "Day 22"?
>
> I thought the default varchar expression length was 10 characters.

Where did you read this? I believe if not specified EDB defaults it to 0
actually.

The actual error IMHO is that "SELECT cast(22.0 as VarChar)" returns
"22" as it should return "" (blank string).

Raul

Barry et al


I decided to get involved here and did a quick experiment (I really don't understand why people don't do this more, EDBManager is a superb tool for it)

select
_invoicevalue,
cast('XX' as varchar)+cast(_invoicevalue as varchar),
'XX'+cast(_invoicevalue as varchar),
length(cast(_invoicevalue as varchar)),
cast('XX' as varchar)+cast(22.0 as varchar),
'XX'+cast(22.0 as varchar),
length(cast(22.0 as varchar)),
cast(_invoicevalue as varchar),
cast(22.0 as varchar)
from invoices

_invoicevalue is DECIMAL(19,1)

The values for the first row are

_invoicevalue, = 2643.75
cast('XX' as varchar)+cast(_invoicevalue as varchar) = XX2643.75
'XX'+cast(_invoicevalue as varchar), = XX
length(cast(_invoicevalue as varchar)) = 7
cast('XX' as varchar)+cast(22.0 as varchar) = XX22
'XX'+cast(22.0 as varchar) = XX
length(cast(22.0 as varchar)) = 2
cast(_invoicevalue as varchar) = 2643.75
cast(22.0 as varchar) = 22 (HARD LUCK ADAM

You can see that the default length of an unqualified varchar is not the problem (or at least I don't think it is). The problem seems to be joining things together when the types are not explicitly known, and yes I know that ElevateDB should be able to work it out, after all I can see its a character variable so why can't it

Here is a guess ElevateDB takes the first item to be joined looks at it and sees CHAR(2) (or in Barry's case CHAR(3)) and then uses that to format the combined expression. Since the second part of the expression is an unqualified VARCHAR it extends the CHAR by 0 (ElevateDB doesn't have any better information from what its been provided) so you end up formatting the combined string as CHAR(2). If you specify that the first part of the expression is also a varchar then the overall rules about length of varchars kicks in.

I think I'm right because

cast('XX' as char(2))+cast(_invoicevalue as varchar),

results in 'XX'

Wether this is a bug,  Tim's implementation, WAD to the spec, or something else I have no idea.

I also remember from an earlier discussion with Tim that there is a default maximum length for an unqualified varchar and I seem to recall its 60, but my memory could well have it wrong.. I don't know about a default minimum length.


Roy Lambert

Adam Brett wrote:

>>Barry wrote:
>>Why does the statement:
>>1) select 'Day '+cast(22.0 as VarChar);
>>not work as expected?

>I am only guessing, but I would say 22.0 is a FLOAT, and this (in SQL terms) would be represented in memory as >a number of bytes & a large number of decimal places, so you have to specify the size of the varchar to avoid it >returning:

>Day 22.00000000000000000000000000

>therefore the default is to do nothing, rather than return the confusing string of 0000000s.

Interesting take on it. So that peaked my interest and discovered:

select 'Day'+Cast(floor(22.0) as VarChar)
returns 'Day', or at least I thought it did.

Some more sleuthing showed using either:

select length('Day'+Cast(floor(22.0) as VarChar))!
select length('Day'+Cast(22.0 as VarChar))!

both returned 5. So the result being returned is really 'Day  '. The length of the result is 5, which it should be. It just forgot to include the '22'. Hmmm.

Barry

Raul wrote:

>Where did you read this? I believe if not specified EDB defaults it to 0
>actually.

In a Stored Procedure or script you can define:

Declare _x VarChar Default NULL;

or have VarChar of length 0 as a parameter to a function or procedure, or even as a return value, and it can handle VarChar of any size. You don't need to specify VarChar(255) to handle large strings. Just leave out the "(255)" and it can handle clob size strings.

But I've noticed a lot of times in an SQL statement if I have a string expression when returning rows from a table, the value returned gets truncated to only 10 characters. I thought this was the norm. If it happens again I will definitely post an example.

Barry

Roy,

Gee, I fail to check back and a couple of days later I see the detectives busy solving "The case of the missing characters".  

I think Roy, you may have solved the problem.

I noticed:

select cast('xx' as VarChar(2)) + Cast(22.0 as VarChar)!   'xx  '
select cast('xx' as VarChar(3)) + Cast(22.0 as VarChar)!  'xx2 '
select cast('xx' as VarChar(4)) + Cast(22.0 as VarChar)!  'xx22'
select cast('xx' as VarChar(22)) + Cast(22.0 as VarChar)!  'xx22'
select cast('xx' as VarChar) + Cast(22.0 as VarChar)!  'xx22'


>Wether this is a bug,  Tim's implementation, WAD to the spec, or something else I have no idea.<

To me it looks like a bug. I see no reason why it should operate this way, unless it is to sell more aspirin.

Barry

Barry


>To me it looks like a bug. I see no reason why it should operate this way, unless it is to sell more aspirin.

I could take wither side of teh argumentt.

You're adding a VARCHAR to a CHAR and since you haven't specified the resultants type its defaulting to CHAR

on the other hand

If you add a DECIMAL(19,1) to an INTEGER you end up with a DECIMAL(19,1) not an INTEGER

and on the gripping hand

If you add a time to a date it tells you off

Here have some Co-codamol (much better than asprin)

Roy