converting nvarchar to int

[dmorand]

I'm trying to convert a data type of nvarchar to an int data type, but I keep getting errors when I try.

Here's my latest try:

cast(convert(int,submitter) as int) as 'Submitter' 

Any thoughts on how I can do this?

[dmorand]

I'm trying to convert a data type of nvarchar to an int data type, but I keep getting errors when I try.

Here's my latest try:

cast(convert(int,submitter) as int) as 'Submitter' 

Any thoughts on how I can do this?

Nevermind, it does work....it's coldfusion that's being dumb.

[dmorand]

Actually I'm wrong. Some of the values in the table are text, and some are numbers so the fields that have text in them are causing the query to crash.

Any idea on how to only retrieve the fields in the rows that have numerical values in them?

[ck9663]

Try something like:

select 
      case 
          when isnumeric(submitter) = 1 then 
                  cast(submitter AS int)
          else
                  NULL
     end
 
AS 'Submitter'
from YourTable
 

Just change the else part as necessary.

-- CK

[dmorand]

Try something like:

select 
      case 
          when isnumeric(submitter) = 1 then 
                  cast(submitter AS int)
          else
                  NULL
     end
 
AS 'Submitter'
from YourTable
 

Just change the else part as necessary.

-- CK

That's a good idea, thanks!

[dmorand]

That's a good idea, thanks!

Thanks it worked out perfectly!