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
我正在使用函数sp_spaceused来获取数据库中所有表的详细信息。 index_size列是VARCHAR,最后返回完成'KB',但我想以MB显示。我需要知道的是如何剥离KB,我可以做其余的事! :d
UPDATE: I don't feel this is a duplicate of the other question suggested as I was looking for a SQL only solution, which was given in this thread.
更新:我不认为这是建议的其他问题的重复,因为我正在寻找一个SQL唯一的解决方案,这是在这个线程中给出的。
4 个解决方案
#1
REPLACE(column, 'KB', ''). No need for LEN and other stuff
REPLACE(列,'KB','')。不需要LEN和其他东西
On SQL 2005, this will give you the "reserved" value:
在SQL 2005上,这将为您提供“保留”值:
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
更多的调查应该允许您从catlog视图中读取索引与数据空间
#2
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)
#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
#4
General solution for T-SQL (SS 2008+), to remove all but a set of allowed characters:
T-SQL(SS 2008+)的一般解决方案,删除除一组允许的字符之外的所有字符:
DECLARE @StrIn varchar(20)='(323)-555-1212'; -- input value
DECLARE @Allowed varchar(20)='%[0123456789]%'; -- pattern for allowed characters.
DECLARE @Result varchar(20)=''; -- result
DECLARE @I int = patindex(@Allowed, @StrIn);
WHILE (@I>0)
begin
SET @Result = @Result + SUBSTRING(@StrIn, @I, 1); -- add allowed charcter.
set @StrIn = SUBSTRING(@StrIn, @I+1, 20); -- take rest of string.
SET @i = patindex(@Allowed, @StrIn);
END
PRINT @Result;
This could easily be encapsulated into a scalar function. A completely general function would accept the list of characters allowed, or you could hard-code for special purpose (like this one).
这可以很容易地封装到标量函数中。一个完全通用的函数会接受允许的字符列表,或者你可以为特殊目的硬编码(比如这个)。
#1
REPLACE(column, 'KB', ''). No need for LEN and other stuff
REPLACE(列,'KB','')。不需要LEN和其他东西
On SQL 2005, this will give you the "reserved" value:
在SQL 2005上,这将为您提供“保留”值:
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
更多的调查应该允许您从catlog视图中读取索引与数据空间
#2
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)
#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
#4
General solution for T-SQL (SS 2008+), to remove all but a set of allowed characters:
T-SQL(SS 2008+)的一般解决方案,删除除一组允许的字符之外的所有字符:
DECLARE @StrIn varchar(20)='(323)-555-1212'; -- input value
DECLARE @Allowed varchar(20)='%[0123456789]%'; -- pattern for allowed characters.
DECLARE @Result varchar(20)=''; -- result
DECLARE @I int = patindex(@Allowed, @StrIn);
WHILE (@I>0)
begin
SET @Result = @Result + SUBSTRING(@StrIn, @I, 1); -- add allowed charcter.
set @StrIn = SUBSTRING(@StrIn, @I+1, 20); -- take rest of string.
SET @i = patindex(@Allowed, @StrIn);
END
PRINT @Result;
This could easily be encapsulated into a scalar function. A completely general function would accept the list of characters allowed, or you could hard-code for special purpose (like this one).
这可以很容易地封装到标量函数中。一个完全通用的函数会接受允许的字符列表,或者你可以为特殊目的硬编码(比如这个)。