[FIXED] How to move decimal place 2 positions to the right in SQL, not converting or rounding

Issue

I am trying convert a version number to the correct format as it is appending with other data in the database. I am using substring to pull the last 6 digits of the messed up version number and what I get is the last 6 digits but the decimal place is 2 digits further up than it should be do to the way it is formatted in the database. What I am trying to do is just move that decimal place two digits over if possible. This is the driver version I have

25.21.14.2600

What I need is the last 6 with two decimal points to the right

426.00

I have looked for answers on this and have tried what I have seen which is

CONVERT(DECIMAL,SUBSTRING(vc.DriverVersion0,8,6))/100

but this rounds it and moves the decimal place. I have tried using Replace

Replace(SUBSTRING(vc.DriverVersion0,8,6),'.','')

Which that works to remove it but I don't know how I can add a decimal to it after that, would be nice if I could just move but either one works. Any help is appreciated.

Solution

Hmmm . . . I'm not sure which way you want to move it. But you can use stuff() and charindex():

select stuff(replace(str, '.', ''), charindex('.', str) + 2, 0, '.')

I'm not sure if you want + 2 or - 2, though.

So this code:

select stuff(replace(str, '.', ''), charindex('.', str) + 2, 0, '.')
from (values ('a.bcdef'), ('123.456')) v(str)

Returns:

abc.def
12345.6

This code:

select stuff(replace(str, '.', ''), charindex('.', str) - 2, 0, '.')
from (values ('abcde.f'), ('123.456')) v(str)

Returns:

abc.def
1.23456

Answered By - Gordon Linoff

Answer Checked By - Katrina (PHPFixing Volunteer)