SQL byte converter function-CodePudding

Is there a function or how would you implement a function that would check the last two characters of a string and convert this string to bytes?

such as '1GB'
convert('1GB')
returns = 1073741824

It needs to check if it's a MB and TB as well.

Thank you!

CodePudding user response：

Here's how I would implement such a function - as a table valued function which you can then use via a correlated subquery, join or apply:

create or alter function ValueToBytes(@input varchar(10))
returns table as

return
    select Try_Convert(bigint,Replace(v.input,v.ext,'')) * multi [Value]
    from (select @input input,Right(@input,2) ext)v
    cross apply (
      select *
      from (values
        ('KB',1024),
        ('MB',1048576),
        ('GB',1073741834),
        ('TB',1099511627776))m(ext,multi)
      where m.ext = v.ext
    )x;

Simple usage:

select * from ValueToBytes('10GB')
select * from ValueToBytes('5MB')