Removing non-numeric characters in T-SQL

Question

I'm using the function sp_spaceused to get the details of all the tables in a DB. The index_size column is VARCHAR returned complete with ' KB' on the end, however I want to to display in MB. All I need to know is how to strip out the KB, I can do the rest! :D

Answer 1

My first thought would be to just store in in a variable and just use substring to remove the last characters.

-- Setup
DECLARE @data VARCHAR(50)
SET @data = '159736 KB'

-- Computation
SET @data = SUBSTRING(@data, 1, LEN(@data)-2)

-- Conversion
SELECT CAST(@data AS INTEGER)

Answer 2

REPLACE(column, 'KB', ''). No need for LEN and other stuff

On SQL 2005, this will give you the "reserved" value:

SELECT
    SUM(au.total_pages) / 128.0 AS UsedMB
FROM
    sys.allocation_units au

Some more investigation should allow you to read index vs data space out of the catlog views too

Answer 3

More generic solution:

-- Test data
DECLARE @StrIn VARCHAR(100), @StrOut VARCHAR(100), @I INT, @Len INT
  SELECT @StrIn = '123m43 5m409', @StrOut = '', @I = 0, @Len = Len(@StrIn)

-- Answer
WHILE (@I < @Len) BEGIN 
  SELECT @I = @I + 1, 
    @StrOut = @StrOut + 
      CASE 
        WHEN (CAST(ASCII(SUBSTRING(@StrIn, @I, 1)) AS INT) BETWEEN 47 AND 58) 
        THEN SUBSTRING(@StrIn, @I, 1) ELSE '' 
      END 
END

SELECT @StrIn, @StrOut

Answer 4

Thanks for the script, it was very helpful. Only one minor issue. The WHILE loop needs to be <= @Len. Otherwise the last digit may be lost.