Converting @myString to @myInt

Hot Topic

Complex join
SSEM - How do you connect to an MDF file?
Multi Value Parameters
Restoring in SQL 2005
ReportBuilder Issues w/ Form Authentication
Row and Cell Security
EnumAvailableSqlServers does not return all instances
rsExecutionNotFound
XCOPY documentation
backwards compatibility of the MDF file 2000 and 2005

SQL Server

Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Problem with creating endpoint in SQL 2005: "Incorrect syntax near 'ENDPOINT'"
C# Pgm: BOL: instance.AcquireConnections(null) HRESULT: 0xC020801C
Service Pack 1 Database Services Installation Failure
How many partitions
User Instance=True causes invalid connection string
can not drop user from database
How do you return query results in a streaming table-valued function?
Customising Dimension Security
Full-Text Search / User Instances

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




Answer this question

Converting @myString to @myInt

  • jkrakowsky

    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

  • Arthur

    > 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...


  • aspic

    > 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


  • Converting @myString to @myInt
Answer this question