CODEWITHSUNDEEP
sqlt-sql
Glyn
Glyn

Reputation: 339

T-SQL to split column results and do maths on the result

I've got a table with filesizes in like so

FileSize
68 bytes
122.80 Kb
23.5 Mb
1.2 Gb

I need to SELECT the results in Mb, so the results look like

Converted FileSize
0.000068
0.1228
23.5
1200

This splits the results down correctly

WITH Splitted AS
(
    SELECT CAST(N'<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([File Size],N' ',N' '),N'&',N'&amp;'),N'<',N'&lt;'),N'>',N'&gt;'),N' ',N'</x><x>') + N'</x>' AS XML) testXML
    FROM Attachment
)
SELECT testXML.value('/x[1]','float') AS Number
      ,testXML.value('/x[2]','nvarchar(max)') AS metric
FROM Splitted

to

Size      Metric
68        bytes
122.8     Kb
23.5      Mb      
1.2       Gb

Is there way to do the conversion on the "Size" column value based on the "Metric" column value within SQL?

Upvotes: 0

Views: 34

Answers (2)

Glyn
Glyn

Reputation: 339

Based on the above which I've marked as the answer, this is what I've used.

SELECT [File Type], [File Size],
      CAST( A.Size * CASE A.Metric WHEN 'bytes' THEN 1.0 / (1024 * 1024)
                                WHEN 'Kb' THEN 1.0 / 1024
                                WHEN 'Mb' THEN 1
                                WHEN 'Gb' THEN 1024
                  END as NUMERIC(10, 2))AS Megabytes
FROM Attachment AA
     CROSS APPLY(
        VALUES( TRY_CONVERT(decimal(10,4),LEFT(AA.[File Size], NULLIF(CHARINDEX(' ',AA.[File Size]),0)-1)),
            STUFF(AA.[File Size],1,NULLIF(CHARINDEX(' ',AA.[File Size]),0),''))
        )A (Size, Metric)

WHERE AA.[File Type] IS NOT NULL
ORDER BY Megabytes ASC

File type   File size   Megabytes
txt         68 bytes    0.00
txt         68 bytes    0.00
PDF         122.80 KB   0.12
PDF         122.80 KB   0.12
txt         23.5 Mb     23.50
PDF         1.2 Gb      1228.80

Upvotes: 0

Thom A
Thom A

Reputation: 95830

One method would be to use CHARINDEX to get the position of the space and split the value into the 2 columns. Then you use a CASE expression to multiple the value by the appropriate amount:

WITH YourTable AS(
    SELECT V.FileSize
    FROM (VALUES('68 bytes'),
                ('122.80 Kb'),
                ('23.5 Mb'),
                ('1.2 Gb'))V(FileSize))
SELECT *,
       V.[Size] * CASE V.Metric WHEN 'bytes' THEN 1. / (1024 * 1024)
                                WHEN 'Kb' THEN 1. / 1024
                                WHEN 'Mb' THEN 1
                                WHEN 'Gb' THEN 1024
                  END AS Megabytes
FROM YourTable YT
     CROSS APPLY(VALUES(TRY_CONVERT(decimal(10,4),LEFT(YT.FileSize,NULLIF(CHARINDEX(' ',YT.FileSize),0)-1)),STUFF(YT.FileSize,1,NULLIF(CHARINDEX(' ',YT.FileSize),0),'')))V(Size,Metric)

Upvotes: 1

Related Questions