Prev: Chris Daten Seminar in Mid-May in Scotland
Next: Testing database
From: bill on 14 Mar 2010 14:29
Thanks for the confirmation Tibor. I was too tired to look at the
code and see if it was actually returning two strings, or just a using
creating some very trick arguments to one function.

For the OP:

If you had this:
SELECT @variable = String1, String2

You could do this (assuming you want a concated result):
SELECT @variable = String1 + String2

This way, you are only assigning one column to the variable, and
you'll avoid the error. If you need to manipulate each piece
independently, then assign each piece to a different variable, fix up
the variables, and then concatenate them for the result.
-----------

As an aside, Microsoft's choice to continue using the "plus sign" for
string concatenation was unfortunate. The double pipe ( || ) is the
standard, and never leads to confusion. Check out the following two
statements:

Statement A: SELECT '1' + '4'

Statement B: SELECT '1' + 4

In MS SQL Server (a product which I really like, but everything has
its shortcomings, and this is one), statement 'A' returns the string
'14', and statement 'B' returns the integer 5. In Oracle, both
statements would return the integer 5. If you wanted to get the
string '14' from Oracle, you would have to say: SELECT '1' || '4'
FROM DUAL. I think this type of inconsistency should be avoided by
the software designers, and simply sticking with the standard
concatenation operator makes it easier to be consistent. Granted,
users and developers ought not to depend on implicit type conversion
anyway, but since the engine offers it, and people do depend on
implicit conversion, it's good to be consistent.).

I don't intend to start a flame war, just to point out something I
consider interesting. For what it is worth, I spent years in Oracle
environments prior to switching to MS SQL Server. On the whole, I
like MS SQL Server better, but they're both good engines.



From: Erland Sommarskog on 14 Mar 2010 15:30
bill (billmaclean1(a)gmail.com) writes:
> In MS SQL Server (a product which I really like, but everything has
> its shortcomings, and this is one), statement 'A' returns the string
> '14', and statement 'B' returns the integer 5. In Oracle, both
> statements would return the integer 5.

Note that had SQL Server used || for concatenation and + only for addition,

SELECT '1' + 4

would have yielded an error, as type conversion in SQL Server always
follows the type hierarchy, and varchar has lower precedence than
integer.

But the real problem, in my opinion, is that there is a implicit conversion
between strings and numbers at all. In SQL 6.5 the above is an error, plain
and simlpe.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: bill on 14 Mar 2010 22:07
Hi Erland,

I agree that it would be better if the engine did not offer implicit
conversion between strings and numbers, but since it does, it should
at least be consistent.

I think I misunderstood your statement "In SQL 6.5 the above is an
error, plain
and simple." If you are referring to the statement SELECT '1' + 4 I
agree that it is ambiguous and not well formed from a logial
standpoint. However, I just ran it in SQL 2005 R2, and received the
answer 5.

Were you referring the bad logic behind the statement, or to the way
SQL Server executes it?

Thanks,

Bill



From: Tibor Karaszi on 15 Mar 2010 02:50
Hi Bill,

6.5 is an old version of SQL Server, the last one from the "old
architecture". This were closer to the Sybase heritage. In 7.0 (released
1998), MS did lots and lots of changes. One of them was to allow for more
implicit type conversions. Many of us don't like it...

> If you are referring to the statement SELECT '1' + 4 I
> agree that it is ambiguous and not well formed from a logial
> standpoint. However, I just ran it in SQL 2005 R2, and received the
> answer 5.

That is documented behavior as of 7.0. Int has higher precedence than
various char, so '1' will be converted to number 1 and they will be
numerically added.

I wouldn't mind || for string concatenation (even though it will probably
have a slow pick up), considering it being ANSI SQL. I doubt, however, it
will have any effect here. As Erland say, '1' || 4 should produce am error
considering the rules in MSSQL for implicit type conversion and type
precedence.


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"bill" <billmaclean1(a)gmail.com> wrote in message
news:6a66d4d4-e4c6-4087-b385-9e5a2143bb0d(a)z11g2000yqz.googlegroups.com...
> Hi Erland,
>
> I agree that it would be better if the engine did not offer implicit
> conversion between strings and numbers, but since it does, it should
> at least be consistent.
>
> I think I misunderstood your statement "In SQL 6.5 the above is an
> error, plain
> and simple." If you are referring to the statement SELECT '1' + 4 I
> agree that it is ambiguous and not well formed from a logial
> standpoint. However, I just ran it in SQL 2005 R2, and received the
> answer 5.
>
> Were you referring the bad logic behind the statement, or to the way
> SQL Server executes it?
>
> Thanks,
>
> Bill
>
>
>
From: Erland Sommarskog on 15 Mar 2010 04:55
bill (billmaclean1(a)gmail.com) writes:
> I think I misunderstood your statement "In SQL 6.5 the above is an
> error, plain
> and simple." If you are referring to the statement SELECT '1' + 4 I
> agree that it is ambiguous and not well formed from a logial
> standpoint. However, I just ran it in SQL 2005 R2, and received the
> answer 5.

I said 6.5, not 10.5. :-)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Chris Daten Seminar in Mid-May in Scotland
Next: Testing database