# Special Formatting of Numbers

I need help formatting numbers in a specific way.

If a number has 3 digits or less, I would like it to remain the same.

If a number has more than three digits, I would like it place a decimal after the third decimal.

For instance:

``````123 --> Stays the same
1234 --> 123.4
1234567 --> 123.4567
``````

I am using SQL Server and want to `UPDATE` the value in the table. The value is stored as a numeric.

• Do you mean SQL Server 2008? – JNK Jun 13 '12 at 18:49
• You say it's stored as numeric - can you clarify? What is the source type of the value, and what should the target type be? – Mark S. Rasmussen Sep 11 '12 at 12:34

Assuming they are all ints and you always want the first three digits to appear before the decimal:

``````WITH n(r) AS (
SELECT 123 UNION ALL SELECT 1234 UNION ALL SELECT 1234567
)
SELECT LEFT(r, 3) + CASE
WHEN LEN(r) > 3 THEN '.' + SUBSTRING(RTRIM(r),4,38) ELSE '' END
FROM n;
``````
• `SUBSTRING(...,12)` because an `INT` can be no more than 12 digits long, right? Is there an easy way to generalize this to handle numbers of any type and precision? – Nick Chammas Jun 13 '12 at 19:05
• Sure, make it 38 :-) My point about them being ints was that they weren't strings and/or didn't already have a decimal place potentially. – Aaron Bertrand Jun 13 '12 at 19:06
• Interesting use of a CTE to answer this question, @AaronBertrand I would never have thought of a CTE as a temporary table in that way. – Max Vernon Sep 11 '12 at 3:00
``````update YourTable
set YourNumericCol =
case
when len(cast(YourIntCol as varchar(30))) > 3
then cast
(
left(cast(YourIntCol as varchar(30)), 3) +
'.' +
right(cast(YourIntCol as varchar(30)),
len(cast(YourIntCol as varchar(30))) - 3)
as numeric(12, 4)
)
else
YourIntCol
end
``````

``````SELECT y, ISNULL( STUFF( y, 4, 0, '.' ), y )
FROM
(
VALUES ( 123 ), ( 1234 ), ( 1234567 )
) x(y)
``````

Since the number is numeric and will be stored in the same numeric column, this is is an arithmetic transformation rather than a formatting change. The value does need to be converted to a character in order to get its length, but not for the value change itself.

``````UPDATE t1 SET c1 = c1/Power(10,Len(Floor(c1/100))-1);
``````

or

``````WITH t1(c1) AS (
SELECT 12 UNION ALL
SELECT 123 UNION ALL
SELECT 1234 UNION ALL
SELECT 12345 UNION ALL
SELECT 123456 UNION ALL
SELECT 1234567
)
SELECT c1/Power(10,Len(Floor(c1/100))-1) FROM t1;
``````