SQL Server 2008 Prior String Extract
Posted
by
Saidur Rahman
on Stack Overflow
See other posts from Stack Overflow
or by Saidur Rahman
Published on 2012-03-19T01:44:29Z
Indexed on
2012/03/19
2:03 UTC
Read the original article
Hit count: 174
sql-server-2008
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.
© Stack Overflow or respective owner