## Thursday, December 2, 2010

### String Concatenation and Implicit Conversions

I needed to modify a SQL function to strip some leading zeros in order to concatenate a string. Part of this process involved dealing with pluralization of the number of cents (I'm picky that way).

``````
SELECT
CASE
WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1 THEN ''
WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1 THEN 's'
ELSE ''
END
``````

The idea is that if the value (0.30 representing cents) translates into a number greater than 1, the resulting concatenated sentence will read something like "The discount is 30 cents." If the value equals 1, however, it would read "The discount is 1 cent."

When the above is executed, I get an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0.03' to data type int.

At first this baffled me. Data type int?? I wasn't trying to obtain an integer value, I was ultimately trying to obtain a varchar for this string I'm building.

Then it hit me, SQL is performing an implicit conversion when it is comparing the value resulting from the conversion first to decimal, then float, and finally to varchar, with 1.

Lesson learned, when attempting to compare two varchar with numeric values, make sure that if the value being tested is decimal, that the static value being used to compare it against is also decimal.

I made the following changes and now it works flawlessly.

``````
SELECT
CASE
WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) = 1.0 THEN ''
WHEN CAST(CAST(CONVERT(decimal(18,6), 0.30) * 100 AS float) AS varchar(10)) > 1.0 THEN 's'
ELSE ''
END
``````

` `