Conver NVARCHAR to INT

Question

Post reply

Conver NVARCHAR to INT

Viky123

Say Hey Kid

Points: 663
More actions
May 15, 2014 at 12:58 am

#146876

Declare @a AS NVARCHAR(16)
SET @a='1'
Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)
ELSE @a
END
This code is working fine but,
Why below code give me error also please give me solution
Declare @a AS NVARCHAR(16)
SET @a='B'
Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)
ELSE @a
END

Viewing 5 posts - 1 through 4 (of 4 total)

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

This will differentiate between the two with out an error:-

DECLARE @a AS NVARCHAR(16)

SET @a = 'B'

IF ISNUMERIC(@A) = 1

BEGIN

SELECT CAST(@A AS INT)

END

ELSE

BEGIN

SELECT @a

END

But I'm not sure if you can fit this in with your existing code. Why are you getting numerics and non-numerics in the same column / parameter?

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Viky123 Say Hey Kid Points: 663 More actions · Answer 2

Thanks for reply,

It is in existing table and i can not change it.

consider there is column A which have datatype Nvarchar

and while selecting if it is numric value i have to convert it in INT type if not then i have to keep it as it is.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Viky123 (5/15/2014)
Declare @a AS NVARCHAR(16)
SET @a='1'
Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)
ELSE @a
END
This code is working fine but,
Why below code give me error also please give me solution
Declare @a AS NVARCHAR(16)
SET @a='B'
Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)
ELSE @a
END

CASE can only return one datatype. Your example attempts to return INT for the first choice and NVARCHAR for the second. Where choices return different datatypes, SQL Server uses data type precedence - which puts INT before NVARCHAR. You can avoid the error by setting the second choice to an explicit INT value e.g. 0, or to NULL:

Declare @a AS NVARCHAR(16)

SET @a = 'B'

Select Case WHEN ISNUMERIC(@A) = 1 THEN Cast(@A AS int)

ELSE NULL --@A

END

Depending on what you want to do, which isn't at all clear, casting the first choice back to character type might be more appropriate:

Select Case WHEN ISNUMERIC(@A) = 1 THEN CAST(Cast(@A AS int) AS NVARCHAR(16))

ELSE @a

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viky123 Say Hey Kid Points: 663 More actions · Answer 4

Viky123

Say Hey Kid

Points: 663

May 15, 2014 at 3:59 am

#1713757

Thanks,

Its working:-)