Change a string = '1 1/4' to '1.2500'

declare @Dimension = '1 1/4' varchar(20)

I want to change Dimension = '1 1/4' to Dimension = '1.2500'. I do not know how to split a varchar into 2 varchar and change one part and then recombine then into a single varchar

UPDATE VF_CasINV_Cost
   SET @Dimension = CASE

        when (@Dimension like '%1/4') then
        (left(@Dimension, charindex(' ', @Dimension, 1) - 1) *
        (substring(@Dimension, 1 + charindex(' ', @Dimension, 1), len(@Dimension)))

           end
where @Dimension like '%1/4'

what would be great to know how to parse the fraction and开发者_如何学Go recal it into decimal on the fly

---

declare @x varchar(100)
select @x = '15 3/165'
select 
    convert(int, substring(@x, 1, charindex(' ', @x))) + (
        convert(decimal(12,4), substring(@x, charindex(' ', @x) + 1, charindex('/', @x) - charindex(' ', @x) - 1)) /
        convert(decimal(12,4), substring(@x, charindex('/', @x) + 1, len(@x) - charindex('/', @x)))
    )

---

Payload beat me to it but one way is to;

declare @Dimension varchar(20) = '1 1/8' 
declare @sql nvarchar(512) = 'set @out=' + replace(@Dimension, ' ',' + ') + '.00'
declare @result decimal(9,4)

execute sp_executesql @sql, N'@value varchar(30), @out decimal(9,4) output', @value=@Dimension, @out=@result output
select @result

---

isolate the fraction with substring and charindexing, then execute a select.

set @stringquery = SELECT "SELECT "+substring(@Dimension,1+charindex(' ',@Dimension,1),len(@Dimension);
execute sp_executesql @stringquery

This should handle and fraction after a space.

actually, thinking about it - to do the whole equation, just replace the space with a + sign and run it through sp_executesql, this should (I havnt tested this right at this point) convert 1 1/4 to 1+1/4 and the standard math engine will do the division before the addition so it will end up as 1.25.

---

This script can handle more variations in dimension (x in the table)

declare @t table(x varchar(20))
insert @t values('1 1/2')
insert @t values('2')
insert @t values('2/3')
insert @t values('22 1/3')


select coalesce(cast(case when isnumeric(n1)=1 then n1 else 0 end + n2 / n3 as real), n1)
from
(
select case when isnumeric(x) = 1 then x else 
left(x, patindex('%_ %', x)) end n1, 
cast(stuff(stuff(x, patindex('%/%', x), charindex('/',reverse(x)), ''), 1,charindex(' ',x),'') as real) n2,
case when patindex('%_/%', reverse(x)) = 0 then null else 
cast(right(x, patindex('%_/%', reverse(x))) as real) end n3
from @t
) a