Solved

sql server Implicit cast or convert data type

Posted on 2014-03-16
10
328 Views
Last Modified: 2014-03-17
Hi Expert,

I try to test @X, the following code has error, I like to know @X is Int or character data type here?

declare @X varchar(5);
set @X = 12345;
SELECT 
CASE 
      WHEN isnumeric(@X)=1
       THEN 'Yes'
       ELSE 'No'
END AS [Test],

Open in new window


Thanks in advance
0
Comment
Question by:dshi15
  • 5
  • 4
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39932985
Since @x is declared a varchar, to assign a value to it you have to surround that value with single quote marks

set @x = '12345'
0
 
LVL 1

Author Comment

by:dshi15
ID: 39933013
Thank you , I knew that, but I want to know when implicit convert happened, or not?


declare @X varchar(5);
set @X = 12345;
SELECT @X;

This code work and result is 12345. I like to know this 12345 is varchar type or not?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39933182
> declare @X varchar(5);
> I like to know this 12345 is varchar type or not?

I really don't understand your question.  @x is explicitly declared as a varchar(5) value in the first line above, and whether the second line assigns it a value to it of '12345', 'wahoo', NULL, or '-1' doesn't change that.

The ISNUMERIC() function will return if the value inside @x can be converted to a number, but that doesn't change the fact that @x is a varchar(5).
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:dshi15
ID: 39933325
I changed "," to ";" now code is working and without error


declare @X varchar(5);
set @X = 12345;
SELECT 
CASE 
      WHEN isnumeric(@X)=1
       THEN 'Yes'
       ELSE 'No'
END AS [Test];

Open in new window


result is Yes, it is means @X is stored a integer 12345?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39933358
The ; at the end of a line is not necessary, with the sole exception I can think of being the line before a Common Table Expression (CTE) statement.

The comma is correct in that it didn't belong there, but the way you posted the code block in the original question I assumed that there was more to the SQL Statement that was not copy-pasted into the question because it was not relevant.
0
 
LVL 1

Author Comment

by:dshi15
ID: 39934069
' ; ' is not important, it is syntax error, but my question is @X is stored a integer 12345? or not, (I try to understand implicit convert data type).
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39934081
>but my question is @X is stored a integer 12345?
NO.

Because @x was declared varchar(5), the 12345 is stored AS A VARCHAR.
The fact that it is also an int is not relevant.

If you wish it to be stored as an integer, which is best practice if all of these values are numeric with no decimals, then you need to declare @x as an int.

Declare @x int
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39934622
>> is @X is stored a integer 12345? <<

No.  Always remember that in SQL Server, like all other DBMSs, the input format doesn't necessarily reflect the internal format of the data.


Btw, to check for integer values, you must LIKE as below, because ISNUMERIC() has to handle many data types besides just int:


SELECT
CASE WHEN x LIKE '%[^0-9]%'
     THEN 'No'
     ELSE 'Yes'
     END AS [Test_X_For_int],
CASE WHEN isnumeric(x) = 0
     THEN 'No'
     ELSE 'Yes'
     END AS [Test_X_For_ISNUMERIC]
FROM (
    SELECT CAST(12345 AS varchar(5)) AS x UNION ALL
    SELECT ',' UNION ALL
    SELECT '1D0'
) AS test_data
0
 
LVL 1

Author Comment

by:dshi15
ID: 39935063
I'm confused, I run this code, and result is 'Yes'

declare @X varchar(5);
set @X = 12345;
SELECT
CASE
      WHEN isnumeric(@X)=1
       THEN 'Yes'
       ELSE 'No'
END AS [Test];

I run this code and all results are 'Yes'


declare @X varchar(5);
set @X = 12345;
CASE WHEN x LIKE '%[^0-9]%'
     THEN 'No'
     ELSE 'Yes'
     END AS [Test_X_For_int],
CASE WHEN isnumeric(x) = 0
     THEN 'No'
     ELSE 'Yes'
     END AS [Test_X_For_ISNUMERIC]
FROM (
    SELECT CAST(12345 AS varchar(5)) AS x UNION ALL
    SELECT ',' UNION ALL
    SELECT '1D0'
) AS test_data
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 39935082
>I run this code, and result is 'Yes'
The VALUE INSIDE @x, which during code execution is '12345', can convert to the integer data type.
This is NOT THE SAME as the variable @x being an integer.

So ... using simple English with as few technical terms as possible, what exactly are you asking us?
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question