Avi Avi - 5 months ago 19
SQL Question

SQL Server how to extract only the numeric

I have those kind of values in my table and I want to retrieve only the numeric value.

How can I do that?

Thanks

\physicaldisk(1 d:)\disk read bytes/sec 49920420.7711372
\physicaldisk(2 e:)\disk read bytes/sec 0
\physicaldisk(0 c:)\disk bytes/sec 1666197.41297793
\physicaldisk(2 e:)\disk bytes/sec 4093.85113753791
\physicaldisk(3 t:)\disk bytes/sec 4605.58252973014
\physicaldisk(2 e:)\disk write bytes/sec 4093.85113753791
\physicaldisk(3 t:)\disk write bytes/sec 4605.58252973014
\physicaldisk(1 d:)\disk write bytes/sec 67073657.0374211
\physicaldisk(3 t:)\disk read bytes/sec 0
\physicaldisk(0 c:)\disk read bytes/sec 1567944.98567702
\physicaldisk(1 d:)\disk bytes/sec 116994077.808558
\physicaldisk(2 e:)\disk reads/sec 0
\physicaldisk(3 t:)\disk reads/sec 0
\physicaldisk(1 d:)\disk reads/sec 57.969571771777
\physicaldisk(3 t:)\disk transfers/sec 8.99527837837919
\physicaldisk(0 c:)\disk transfers/sec 71.9622270270335
\physicaldisk(3 t:)\disk writes/sec 8.99527837837919
\physicaldisk(0 c:)\disk writes/sec 2.9984261261264

Answer

This works in SQL Server:

select REVERSE(left(REVERSE(MyCol), charindex(' ', REVERSE(MyCol)) - 1))
from Table1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([MyCol] varchar(max))
;

INSERT INTO Table1
    ([MyCol])
VALUES
    ('\physicaldisk(1 d:)\disk read bytes/sec              49920420.7711372'),
('\physicaldisk(2 e:)\disk read bytes/sec                             0'),
('\physicaldisk(0 c:)\disk bytes/sec                   1666197.41297793'),
('\physicaldisk(2 e:)\disk bytes/sec                   4093.85113753791'),
('\physicaldisk(3 t:)\disk bytes/sec                   4605.58252973014'),
('\physicaldisk(2 e:)\disk write bytes/sec             4093.85113753791'),
('\physicaldisk(3 t:)\disk write bytes/sec             4605.58252973014'),
('\physicaldisk(1 d:)\disk write bytes/sec             67073657.0374211'),
('\physicaldisk(3 t:)\disk read bytes/sec                             0'),
('\physicaldisk(0 c:)\disk read bytes/sec              1567944.98567702'),
('\physicaldisk(1 d:)\disk bytes/sec                   116994077.808558'),
('\physicaldisk(2 e:)\disk reads/sec                                  0'),
('\physicaldisk(3 t:)\disk reads/sec                                  0'),
('\physicaldisk(1 d:)\disk reads/sec                    57.969571771777'),
('\physicaldisk(3 t:)\disk transfers/sec               8.99527837837919'),
('\physicaldisk(0 c:)\disk transfers/sec               71.9622270270335'),
('\physicaldisk(3 t:)\disk writes/sec                  8.99527837837919'),
('\physicaldisk(0 c:)\disk writes/sec                   2.9984261261264')

;

Query 1:

select REVERSE(left(REVERSE(MyCol), charindex(' ',REVERSE(MyCol))-1))
from Table1

Results:

|                  |
|------------------|
| 49920420.7711372 |
|                0 |
| 1666197.41297793 |
| 4093.85113753791 |
| 4605.58252973014 |
| 4093.85113753791 |
| 4605.58252973014 |
| 67073657.0374211 |
|                0 |
| 1567944.98567702 |
| 116994077.808558 |
|                0 |
|                0 |
|  57.969571771777 |
| 8.99527837837919 |
| 71.9622270270335 |
| 8.99527837837919 |
|  2.9984261261264 |