Why doesn't TestID convert to INT?
Dev and DBA, I think I figured out a bug in SQL 2008 R2, unless one of you explains below. Could you please explain why TestID in this scenario is converted to NUMERIC instead of INT? Please keep in mind that this is just a sample to show you the problem I ran into when trying something more like an automation script that will convert data imported from Excel or Notepad to be nvarchar / float to the appropriate data types, int, numeric, datetime, varchar.
CREATE TABLE #CorruptData(
TestID VARCHAR(100)
);
GO
INSERT INTO #CorruptData
VALUES ('1'),
('2');
SELECT
CASE
WHEN TestID LIKE '[1-9]%' AND TestID NOT LIKE '0%' THEN CAST(TestID AS INT)
WHEN TestID LIKE '0%' THEN CAST(TestID AS NUMERIC(12,2))
END AS TestID
INTO #FixedData
FROM #CorruptData
SELECT *
FROM #FixedData
source to share
Your 1 and 2 have been converted to decimal because of the way the CASE statement returns the type:
CASE
Result Types Returns the highest priority type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence.
Read the full documentation: http://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx
If you really want to see INT, then just discard whatever is returned from the CASE statement to INT:
CAST(CASE
WHEN TestID LIKE '[1-9]%' AND TestID NOT LIKE '0%' THEN CAST(TestID AS INT)
WHEN TestID LIKE '0%' THEN CAST(TestID AS NUMERIC(12,2))
END AS INT) AS TestID
source to share
Ususaly, when you are dealing with inserted or corrupted data, you should make as few assumptions about it as possible, as ansd can let the user who is viewing and modifying such data decide what it is.
Also, as @Louie Bao said, you cannot have the same column in two or more datatypes. You only need to choose one. Since you need to store numeric values, your column must be NUMERIC [].
So your options are:
- Leave it as VARCHAR
- Put the data in 2 different columns
INT
andNumeric[]
- You have a single
NUMERIC
, but to display, you need to convert numbers without decimal part to VARCHAR (), which look like integers.
source to share