I have two columns, one of data type Smallint and the other int. I am trying to concatenate the two columns and a '.' to produce one final value but I keep getting the error:
Conversion failed when converting the varchar value '1.1' to data type int
For instance, if I have '1' in ColumnA and a '1' in ColumnB, I want to concatenate them like CONCAT(ColumnA, '.', ColumnB)
to give me the value '1.1'
.
This is my code:
CAST(CASE
WHEN ColumnA IS NOT NULL AND (ColumnB = 0 OR ColumnB IS NULL)
THEN ColumnA
WHEN ColumnA IS NOT NULL AND ColumnB IS NOT NULL AND ColumnB > 0
THEN CONCAT(ColumnA, '.', ColumnB)
END AS NUMERIC(2, 1)) AS 'VERSION'
I have tried all different variations of a CAST
, but nothing seems to be working. Any help would be much appreciated. Many thanks in advance.
ColumnA is a SMALLINT in the first case so all following cases must also be convertable to INT. If we Cast the first case into a VARCHAR then all following cases can be VARCHAR. CONCAT produces a VARCHAR.
SELECT *, CAST(
CASE WHEN ColumnA IS NOT NULL AND (ColumnB = 0 OR ColumnB IS NULL) THEN CAST(ColumnA as Varchar(10))
WHEN ColumnA IS NOT NULL AND ColumnB IS NOT NULL AND ColumnB > 0 THEN CONCAT(ColumnA, '.', ColumnB)
END
AS NUMERIC(2,1)
) AS 'VERSION'
FROM Example
ColumnA | ColumnB | VERSION |
---|---|---|
1 | 1 | 1.1 |
2 | 2 | 2.2 |
3 | 4 | 3.4 |