Technical Article

Strip Non-numeric characters from string function

,

Sometimes it's necessary to strip non-numeric characters from the string, so you can convert to integer without error. Often it happens when you want to use stored procedure, especially system procedures. The output there can be already with some characters (MB, KB and such), but you want this result to be used for your own purposes as integer or print the result with order by integer part of column. This function is equivalent of Cint or Int in other languages.

You can customize this function by implementing your own rules as far as ASCII characters allowed.

The second script details how it’s used to show all tables with their size-related metrics ordered by Reserved size.

As functions supported only in SQL 2000, this scalar function is to work under SQL 2000 only. 

-- =============================================
-- Create scalar function fnStripNonnumericChars
-- Created by Michael Gladshteyn
-- =============================================
IF EXISTS (SELECT * 
   FROM   sysobjects 
   WHERE  name = N'fnStripNonnumericChars')
DROP FUNCTION fnStripNonnumericChars
GO

CREATE FUNCTION fnStripNonnumericChars 
(@OrigString varchar(255))
RETURNS varchar(255)
AS
BEGIN
/*
32 is a space 
48 to 57 are our numbers 0-9 
65 to 90 are the capital letters A-Z 
97 to 122 are the lowercase letters a-z 

-- usage: select master.dbo.fnStripNonnumericChars('12345 MB')
*/
declare @NewString varchar(255)


Declare @Len int, @Ctr As int, @Ctr2 As int, @Char As varchar(1)

select @Len = Len(@OrigString)
select @NewString=''

select @Ctr2 = 1, @Ctr=1

while @Ctr <= @Len
Begin
    Select @Char = substring(@OrigString, @Ctr, 1)
    If ASCII(@Char) between 48 and 57 
    begin 
Select @NewString=@NewString+@Char
        Select @Ctr2 = @Ctr2 + 1
    end
    select @Ctr=@Ctr+1
CONTINUE
end
If @Ctr2 = 1 
    Select @NewString = NULL


RETURN @NewString

END
GO
--------------------------------------------------
--------------------------------------------------
-- This is how you can use it

SET NOCOUNT ON
create table #spacetaken
(
[name] varchar(255),
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused  varchar(20)
)
declare @Name varchar(255)
declare curTables scroll cursor for
--select [name] from dbo.sysobjects where id = object_id(N'[dbo].[temp2RptDReportTiers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

open curTables

fetch first from curTables into @Name

while (@@FETCH_STATUS <> -1)
begin
   if (@@FETCH_STATUS = 0)
   begin
INSERT #spacetaken
      exec sp_spaceused @Name
   end
   fetch next from curTables into @Name
end

deallocate curTables

Select [name]=convert(varchar(55),[name]), [rows], reserved,data,index_size,unused 
from #spacetaken order by convert(int,master.dbo.fnStripNonnumericChars(reserved)) desc
drop table #spacetaken
SET NOCOUNT OFF

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating