Solved

sql server Implicit cast or convert data type

Posted on 2014-03-16
10
334 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL - format decimal in a string 5 55
sql 2016 Integration Service connecting to 2012 3 92
MS SQL Conditional WHERE clause 3 47
Need a mirrored QA test site 2 91
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 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