Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

4 Answers

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;
share|improve this answer
    
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
1  
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
add comment
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
share|improve this answer
add comment

How about STUFF? eg

SELECT y, ISNULL( STUFF( y, 4, 0, '.' ), y )
FROM
    (
    VALUES ( 123 ), ( 1234 ), ( 1234567 )
    ) x(y)
share|improve this answer
add comment

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;
share|improve this answer
    
sqlfiddle.com/#!3/0e9da/7 –  Leigh Riffel Sep 11 '12 at 17:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.