SQL Server 2008 Prior String Extract
- by Saidur Rahman
I have strings like the ones below in a SQL column. I want to extract them as a Gigabyte amount in aggregate. Example:
Original Column ---------> Expected Output from a TSQL function
-------------------------------------------
$15 / 1GB 24m + Intern 120MB ----------> 1.12 GB
$19.95 / 500MB + $49.95 / 9GB Blackberry -----> 9.5GB
$174.95 Blackberry 24GB + $10 / 1GB Datapack ----> 25GB
$79 / 6GB --> 6GB
Null --> Null
$20 Plan --> 0GB
Note: for our purpose, 1000MB = 1 GB (not 1024).
The pattern is numbers followed by GB/MB, usually they are combined like 1GB (without any space but may sometimes may contain a space, it is not particularly important if hard to implement for this exception).
Sometimes there are up to three or four instances of GB/MB occurring in the same string which are usually separated by a + sign (see row 2 and 3 of my example above).
I have seen how we extract the dollar values in one of the answers where numbers were followed by $ or extract all integers in a string but I don't want to extract the dollar values or all the integers in a string. I just want the sum of GB/MB in the string.
