CODEWITHSUNDEEP
sqlsql-servert-sql
clmcleod
clmcleod

Reputation: 89

How do I format numbers in a SQL table?

I need help formatting numbers in a specific way.

If a number has three decimal places or less, I would like it to remain the same.

If a number has more than three significant figures, I would like all numbers after the third significant figure to be the fractional part of the number. 

123 --> Stays the same
1234 --> 123.4

How can this be done?

EDIT:

1234567 --> 123.4567

I am on SQL 2007, wishing to UPDATE the value in the table. The value is stored as a numeric.

Upvotes: 0

Views: 1010

Answers (5)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I answered this on a cross-post elsewhere, but for completeness:

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;

Upvotes: 1

Holger Brandt
Holger Brandt

Reputation: 4354

Here is a numeric solution:

UPDATE T SET NUM = NUM/POWER(10,FLOOR(LOG10(NUM))-2)
WHERE NUM>=1000

Or the SELECT statement:

SELECT NUM, CASE WHEN NUM<1000 THEN NUM
    ELSE NUM/POWER(10,FLOOR(LOG10(NUM))-2) 
    END AS NewNUM
FROM T

Note that the exact results can vary depending on the data type of NUM. If it is a FLOAT field, it might round the last decimal if NUM gets too large. If it is of type NUMERIC, it will add zero's to the end. If DECIMAL, you need to be careful of the precision. Note that this applies to all the update solutions already mentioned.

Upvotes: 2

John Dewey
John Dewey

Reputation: 7093

Assuming strings of only integer values:

SELECT CASE WHEN LEN(Num) <= 3 THEN Num
    ELSE STUFF(Num,4,0,'.')
    END
FROM (VALUES('1234567'),('123'),('1234'),('12')) t(Num) --some sample values

Result:

123.4567
123
123.4
12

Upvotes: 1

N West
N West

Reputation: 6819

You could do this with strings.

CREATE TABLE T
( NUM NUMERIC(38,19) );

INSERT INTO T (NUM) VALUES ( 123456789 );
INSERT INTO T (NUM) VALUES ( 12345 );
INSERT INTO T (NUM) VALUES ( 123 );
INSERT INTO T (NUM) VALUES ( 1 );


SELECT CAST(
   CASE WHEN NUM < 999 THEN CAST(FLOOR(NUM) AS VARCHAR) 
        ELSE SUBSTRING(CAST(NUM AS VARCHAR), 1, 3) + '.' 
           + SUBSTRING(CAST(FLOOR(NUM) AS VARCHAR), 4, LEN(CAST(NUM AS VARCHAR)) - 3) 
    END AS NUMERIC(38, 19))
FROM T

UPDATE T
SET NUM = CAST(CASE WHEN NUM < 999 THEN CAST(FLOOR(NUM) AS VARCHAR) 
        ELSE SUBSTRING(CAST(NUM AS VARCHAR), 1, 3) + '.' 
           + SUBSTRING(CAST(FLOOR(NUM) AS VARCHAR), 4, LEN(CAST(NUM AS VARCHAR)) - 3) 
    END AS NUMERIC(38, 19));

I've put a working example on SQLFiddle.

Upvotes: 1

codingbiz
codingbiz

Reputation: 26376

This could work

SELECT 
   CASE WHEN Num > 999 THEN Num/10
   ELSE
      Num
   END As Num

There could be a better way, but this is what I could think of

Upvotes: 1

Related Questions