SQL : return value in required format

Question

select myvalue from mytable where id=1;

it return a value of 10 digits(Ex: 1234567890).
I want these value in 1234.56.7890 format as return value.

means myvalue can return value in required format and not in stored format.

Can anyone help me. You ideas are very wellcome

Answer 1

Try to use the Format function.

Answer 2

It is littlebit expensive command. but it will work if your decimal position is fixed and myvalue is always going to be 10 digits

SELECT LEFT(CONVERT(varchar(10),myvalue),4) + '.' +
SUBSTRING(CONVERT(varchar(10),myvalue),5,2) + '.' +
SUBSTRING(CONVERT(varchar(10),myvalue),7,4)

Answer 3

Here's a slightly confusing alternative:

SELECT REVERSE(
  INSERT(
    INSERT(REVERSE(1234567890), 5, 0, '.'),
      8, 0, '.')) AS formatted;

+--------------+
| formatted    |
+--------------+
| 1234.56.7890 |
+--------------+

This works even for shorter numbers - e.g. 123.