Removing non-numeric characters in T-SQL

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

From stackoverflow

• 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)
  

• 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

• 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
  

• 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.