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