Andrew February 2016

Insert character into Column Data

I have an existing table that contains numerical data within a column (column1 for example).

The data consists of 5 to 9 characters:

ex. 12345
    1234567
    123456789

I need to insert a comma before the last 3 characters:

ex. 12,345
    1234,567
    123456,789

This would be on a SQL Server.

Answers


Kamran Farzami February 2016

This should do it:

DECLARE @SomeNumber BIGINT
SET @SomeNumber = 1234567891234

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@SomeNumber),1), '.00','')

First we convert the data to the datatype MONEY, this way we get the comma at the thousands:

CONVERT(MONEY,@SomeNumber) 

Then we convert the MONEY datatype to VARCHAR datatype:

CONVERT(VARCHAR,CONVERT(MONEY,@SomeNumber),1)

Then we remove the ".00" from the string by using the REPLACE function and replacing it with an empty string '':

REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@SomeNumber),1), '.00','')

Post Status

Asked in February 2016
Viewed 2,001 times
Voted 14
Answered 1 times

Search




Leave an answer