Solved

sql server Implicit cast or convert data type

Posted on 2014-03-16
10
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 66

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 66

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 66

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 66

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 66

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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