Ad

SQL Byte Converter Function

- 1 answer

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!

Ad

Answer

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
join (
    select ext, multi
    from (values
      ('KB',Power(Convert(bigint,1024),1)),
      ('MB',Power(convert(bigint,1024),2)),
      ('GB',Power(convert(bigint,1024),3)),
      ('TB',Power(convert(bigint,1024),4)),
      ('PB',Power(convert(bigint,1024),5)),
      ('EB',Power(convert(bigint,1024),6))
    )m(ext,multi)
)m on m.ext=v.ext

Simple usage:

select * from ValueToBytes('10GB')
select * from ValueToBytes('5MB')
Ad
source: stackoverflow.com
Ad