Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server Implicit cast or convert data type

Posted on 2014-03-16
10
Medium Priority
?
350 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
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 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 1600 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

596 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