Solved

sql server Implicit cast or convert data type

Posted on 2014-03-16
10
329 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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