Special Formatting of Numbers

Question

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.

Answer 1

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;

Answer 2

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

Answer 3

How about STUFF? eg

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

Answer 4

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;