Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

concatenation Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 8:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 3,766, Visits: 3,584
EL Jerry (8/21/2012)
Nice question, learned something new today. I was quite sure the answer would be 251, but then I remembered data type precedence may be tricky.

Toreador (8/21/2012)
sestell1 (8/21/2012)
Implicit conversion isn't always your friend.


Implicit conversion isn't ever your friend!

Implicit conversion isn't even your friend!

I unfriended Implicit Conversion long ago.
Post #1347835
Posted Tuesday, August 21, 2012 8:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:58 AM
Points: 819, Visits: 832
I got it wrong on the assumption that data type conversion would take precedence over concatenation or addition. Now I understand that while I'm correct about that, the precedence does not apply to the entire statement at once, but rather on a series of left-to-right pairs. Makes sense, I suppose.

Fortunately for me I would never consider doing this implicitly. That's just asking for trouble.

ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1347836
Posted Tuesday, August 21, 2012 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 1,343, Visits: 373
I am not a T-SQL person since I am primarily an Oracle DBA, but I did notice the single quotes and it make me think. The equivilent SQL in Oracle produces the same results: select '130' || '120' + 1 from dual;

HTH -- Mark D Powell --
Post #1347850
Posted Tuesday, August 21, 2012 9:09 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:38 AM
Points: 698, Visits: 507
Great tricky question!

"Be brave. Take risks. Nothing can substitute experience."
Post #1347858
Posted Tuesday, August 21, 2012 9:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:34 AM
Points: 701, Visits: 1,140
Try this one: SELECT 1+'130'+'120'+1. It comes up 252! Implicit conversion are very dangerous.
Post #1347875
Posted Tuesday, August 21, 2012 9:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
(Bob Brown) (8/21/2012)
Try this one: SELECT 1+'130'+'120'+1. It comes up 252! Implicit conversion are very dangerous.


I would expect it to.

1 + '130' = 131 ('130' converted to int)
+ '120' = 251 ('120' converted to int because the result above is an int)
+ 1 = 252 :)




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1347885
Posted Tuesday, August 21, 2012 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 22,504, Visits: 30,206
ronmoses (8/21/2012)
I got it wrong on the assumption that data type conversion would take precedence over concatenation or addition. Now I understand that while I'm correct about that, the precedence does not apply to the entire statement at once, but rather on a series of left-to-right pairs. Makes sense, I suppose.

Fortunately for me I would never consider doing this implicitly. That's just asking for trouble.

ron


Bit me for the same reason. Agree, I wouldn't do this implicitly either.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1347894
Posted Tuesday, August 21, 2012 10:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 4,244, Visits: 3,321
Really interesting one - thanks!
Post #1347916
Posted Tuesday, August 21, 2012 11:00 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, April 04, 2014 6:01 PM
Points: 642, Visits: 376
It's very interesting how this starts as a concatenation theme, but now it has become an implicit conversion issue. By the way, the only implicit conversion in wich I trust is string to datetime. It will always works if the string provided is in 'yyyymmdd' format.

declare @s nvarchar(8)
, @d1 datetime
, @d2 datetime
, @d3 datetime

set @s = '20120821'

set dateformat dmy;
set @d1 = @s
select @d1 as date1

set dateformat mdy;
set @d2 = @s
select @d2 as date2

set dateformat ymd;
set @d3 = @s
select @d3 as date3

Post #1347955
Posted Tuesday, August 21, 2012 11:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 20,462, Visits: 14,088
Toreador (8/21/2012)
sestell1 (8/21/2012)
Implicit conversion isn't always your friend.


Implicit conversion isn't ever your friend!


+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1347973
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse