Converting @myString to @myInt

Here's a fun one. BOL says that using CONVERT or CAST to change a string into a number won't work.

The following "works" and I'm assuming it is because the compiler can verify that the string value is in fact a number, even with the Unicode N:

declare @.col2 int

select @.col2 = convert (int, N'12345')

======================================

Here's what I need to work, or I need a creative work-around if someone has an interesting idea:

declare @.col2 int,
@.myString nvarchar(100)

select @.col2 = '12345'
select @.myString = convert(int, @.col2)

Msg 245, Level 16, State 1, Line 67
Conversion failed when converting the nvarchar value '12345' to data type int.

I'm assuming that since the compiler cannot pre-determine the value of @.col2 that it won't allow this.

I understand the responsibility to ensure that the converted string is actually a number. I can put something together as a pre-check for this.

Any suggestions or creative work-arounds would be helpful!

Thanks!

Doug B

> declare @.col2 int,

> @.myString nvarchar(100)

>

> select @.col2 = '12345'

> select @.myString = convert(int, @.col2)

>

> Msg 245, Level 16, State 1, Line 67

> Conversion failed when converting the nvarchar value '12345' to data

> type int.

This works fine for me on SQL Server 2000 (8.00.2151) and SQL Server 2005

(9.00.1399).

A

|||Thanks so much...my example was an attempt to simplify the question, so the code wasn't "exactly" what I was running. Once I realized it should have worked, I found a bug in a substring elsewhere.

Looks like you can convert from a string to a number after all, as long as it is a valid number.

Thanks!

DB|||

> Thanks so much...my example was an attempt to simplify the question,

Don't do that. The people here who are trying to help do not need it.

Whether your SQL is 4 lines or 40, we are still going to cut & paste into QA

or SSMS and run it, and then we will find the actual problem instead of

wasting time asking for more information...