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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.