TRY_CONVERT large numbers

Question

Post reply

TRY_CONVERT large numbers

Steve Jones - SSC Editor

SSC Guru

Points: 737892
More actions
March 1, 2016 at 10:17 pm

#73660

Comments posted to this topic are about the item TRY_CONVERT large numbers
March 2, 2016 at 12:25 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Nice question Steve, a very good reminder i almost got it wrong but the inner voice came at the right time

Carlo Romagnano SSC-Insane Points: 22745 More actions · Answer 2

The strange thing is that this code returns NULL using NVARCHAR(1)

SELECT TRY_CONVERT(NVARCHAR(1), 234523)

jiken Old Hand Points: 314 More actions · Answer 3

When execute below code then error message come, not * (star).

SELECT TRY_CONVERT(VARCHAR(1), 234523)

An error

Msg 195, Level 15, State 10, Line 1

'VARCHAR' is not a recognized built-in function name.

When execute below code then answer come as *.

SELECT CONVERT(VARCHAR(1), 234523)

Result

*

So, you cut wrong point.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

A nice, simple question to start the day. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Carlo Romagnano SSC-Insane Points: 22745 More actions · Answer 5

When execute below code then error message come, not * (star).
SELECT TRY_CONVERT(VARCHAR(1), 234523)
An error
Msg 195, Level 15, State 10, Line 1
'VARCHAR' is not a recognized built-in function name.
When execute below code then answer come as *.
SELECT CONVERT(VARCHAR(1), 234523)
Result
*
So, you cut wrong point.

What's the version of sqlserver?

jiken Old Hand Points: 314 More actions · Answer 6

jiken

Old Hand

Points: 314

March 2, 2016 at 3:18 am

#1862324

SQL Server 2012

patrick.mccausland Old Hand Points: 311 More actions · Answer 7

Got the same result, an error

Tried SELECT TRY_CINVERT(FLOAT, 234523) and got

Msg 195, Level 15, State 10, Line 2

'TRY_CONVERT' is not a recognized built-in function name.

jiken Old Hand Points: 314 More actions · Answer 8

Carlo Romagnano (3/2/2016)
When execute below code then error message come, not * (star).
SELECT TRY_CONVERT(VARCHAR(1), 234523)
An error
Msg 195, Level 15, State 10, Line 1
'VARCHAR' is not a recognized built-in function name.
When execute below code then answer come as *.
SELECT CONVERT(VARCHAR(1), 234523)
Result
*
So, you cut wrong point.
What's the version of sqlserver?

SQL Server 2012

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

jiken (3/2/2016)
When execute below code then error message come, not * (star).
SELECT TRY_CONVERT(VARCHAR(1), 234523)
An error
Msg 195, Level 15, State 10, Line 1
'VARCHAR' is not a recognized built-in function name.
When execute below code then answer come as *.
SELECT CONVERT(VARCHAR(1), 234523)
Result
*
So, you cut wrong point.

Try running it in a database with the compatibility level set to 110 (SQL Server 2012) or higher.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

patrick.mccausland (3/2/2016)
Got the same result, an error
Tried SELECT TRY_CINVERT(FLOAT, 234523) and got
Msg 195, Level 15, State 10, Line 2
'TRY_CONVERT' is not a recognized built-in function name.

Try running it on SQL Server 2012 or higher.

(As indicated in the question)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

patrick.mccausland Old Hand Points: 311 More actions · Answer 11

Mea culpa,

I AM on SQL 12 server, however, I was in a database running at compatibility level 9.

Changed to 12 and got the expected result.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

Nice catch, Hugo. The first one with VARCHAR being the problem is one I still don't get, but the TRY_CONVERT bring the problem makes sense.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

Ed Wagner (3/2/2016)
Nice catch, Hugo. The first one with VARCHAR being the problem is one I still don't get, but the TRY_CONVERT bring the problem makes sense.

TRY_CONVERT was introduced as a reserved keyword in compat level 110. Older compat levels do not recognise it as a keyword, so they assume it's a user-defined object.

The result is that SELECT TRY_CONVERT(varchar(1), 1234) is evaluated just the same way as for instance SELECT asfsd(rtrt(), 123) - and apparently the evaluation starts at the innermost level because this raises an error about rtrt not being recognised; replace this with just a number or something and then you'll get an error about asfsd.

(In compat 110 and up, SQL Server knows that TRY_CONVERT is special in that the first parameter is a data type name; for "normal" functions varchar(1) is never a valid argument)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TRY_CONVERT large numbers

Cookies on SQLServerCentral