Is it possible to convert nvarchar to int?

Question

Hello all,

I have tried something like this:

select PREPRO = case when (isnumeric(PREPRO) = 1 and 
PREPRO in ('0','1','-1')) or 
convert(varchar, PREPRO) in ('True','False')
then convert(bit, convert(integer, PREPRO)) else 'No' end
from dbo.TI10SE

The PREPRO contains all "False".

I get this error:

Conversion failed when converting the nvarchar value 'False' to data type int.

Does it mean that an nvarchar can not be converted to an integer ever? I guess its because some data may be lost.

Answer 1

You are trying to convert your PREPRO to an integer, even if the value it holds is True or False:

convert(integer, PREPRO)

You cannot convert the value False to an integer.

The conditional in your when clause evaluates to true when PREPRO is a number within 0, 1, -1 OR when it evaluates to either True or False. In any of these cases, you attempt to convert this value to an integer and then to a bit.

Answer 2

Use CASE to accomplish this:

DECLARE @PREPRO VARCHAR(5)
SET @PREPRO = 'False'
SELECT CASE WHEN @PREPRO = 'False' THEN 0 ELSE 1 END

Answer 3

nvarchar can be converted to an integer if it contains an integer

DECLARE @PREPRO VARCHAR(5)
SET @PREPRO = '10'

SELECT CONVERT(integer, @PREPRO)

T-SQL doesn't know what to associate with 'False' or 'True', so you will have to use a CASE statement like rdkleine said.

Also in this statement:

convert(bit, convert(integer, PREPRO)) else 'No' end

You're going to receive an error, because 'No' is not of type bit, what are you trying to return?