删除 T-SQL 中的非数字字符 [英] Removing non-numeric characters in T-SQL

问题描述

我正在使用函数 sp_spaceused 来获取数据库中所有表的详细信息.index_size 列是 VARCHAR 返回完整的KB"，但我想以 MB 显示.我只需要知道如何去除 KB，剩下的我可以做！:D

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

更新:我不认为这与建议的另一个问题重复，因为我正在寻找该线程中给出的仅 SQL 解决方案.

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.

推荐答案

REPLACE(column, 'KB', '').不需要 LEN 和其他东西

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

在 SQL 2005 上，这将为您提供保留"值:

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

这篇关于删除 T-SQL 中的非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！