How can I add a character into a specified positio

Question

I have a varchar field like:

195500
122222200

I need to change these values to:

1955.00
1222222.00

Answer 1

Query:

SELECT col,
       LEFT(col,len(col)-2) + '.' + RIGHT(col,2) as newcol
FROM Table1

Result:

|       COL |     NEWCOL |
|-----------|------------|
|    195500 |    1955.00 |
| 122222200 | 1222222.00 |

Answer 2

Ran into something similar and came up with this, could probably change this to a function/SP to make it reusable. Scenario faced was inserting a specified character at different positions within the string, for a certain number of times.

/*
 --- passed in string or column,  N'C4CB6B22250B'
 --- desired output, C4:CB:6B:22:25:0D
 --- Test values 
 --- declare @strToChange varchar(50) = N'C4:CB:6B:22:25:0B'
 --- declare @strToChange varchar(50) = N'C4CB6B22250B'
*/


declare @strToChange varchar(50) = N'C4CB6B22250B'
IF(SELECT LEN(@strToChange) - LEN(REPLACE(@strToChange, ':', ''))) > 0
    BEGIN
        ---returns count of specified char
        SELECT LEN(@strToChange) - LEN(REPLACE(@strToChange, ':', ''))
    END
        ELSE
    BEGIN
        declare @charCount int = 5; --# of times to insert the char
        declare @shiftPosition int = 2; -- inital insertion shift
        While(@charCount > 0)
            begin
            SET @strToChange = LEFT(@strToChange,len(@strToChange)- @shiftPosition) + ':' + RIGHT(@strToChange,@shiftPosition)
            SET @charCount = @charCount - 1 --decrement charCount for each added char
            SET @shiftPosition = @shiftPosition + 3 ---increment shift position by 3 for the new char and the chars that were already there
        end
    SELECT @strToChange
END

Answer 3

Please see the following code. You can choose the symbols and index in variable.

 declare @index int,@sym varchar(10)
 set @sym='#'
 set @index=2
 select left(195500,@index) +''+@sym+''+right(195500,len(195500)-@index) 

Answer 4

If you want to add a '.' before the last two digits of your values you can do:

SELECT substring(code,0,len(code)-1)+'.'+substring(code,len(code)-1,len(code))
FROM table1;

sqlfiddle demo

Answer 5

Please Try : select reverse(stuff(reverse(columnName),3,0,'.') ) from yourTable

Answer 6

Please try:

select 
   Col,
   REVERSE(STUFF(REVERSE(Col), 1, 2, LEFT(REVERSE(Col), 2)+'.'))
from YourTable

SQL Fiddle Demo