sql server Implicit cast or convert data type
Hi Expert,
I try to test @X, the following code has error, I like to know @X is Int or character data type here?
Thanks in advance
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],
Thanks in advance
Last Comment
ASKER
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?
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?
> 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).
> 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).
ASKER
I changed "," to ";" now code is working and without error
result is Yes, it is means @X is stored a integer 12345?
declare @X varchar(5);
set @X = 12345;
SELECT
CASE
WHEN isnumeric(@X)=1
THEN 'Yes'
ELSE 'No'
END AS [Test];
result is Yes, it is means @X is stored a integer 12345?
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.
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.
ASKER
' ; ' 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).
>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
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
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
set @x = '12345'