How to control strings with the cast statement of MSSQL
- Often, you will need to control the width of the columns displayed.
- It's always better to control the width of the columns with the front-end, but sometimes you will need to do it in SQL Server.
- You can use the cast, instead of the substring for doing the job.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
SELECT database_id,FILE_ID,type_desc,name,physical_name
FROM sys.master_files;
go
database_id file_id type_desc name physical_name
----------- ----------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 ROWS master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
1 2 LOG mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2 1 ROWS tempdev c:\syv\tempdb.mdf
2 2 LOG templog c:\syv\templog.ldf
3 1 ROWS modeldev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf
3 2 LOG modellog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf
4 1 ROWS MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
4 2 LOG MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf
5 1 ROWS ReportServer C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServer.mdf
5 2 LOG ReportServer_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServer_log.LDF
6 1 ROWS ReportServerTempDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf
6 2 LOG ReportServerTempDB_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB_log.LDF
7 1 ROWS AdventureWorksDW_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf
7 2 LOG AdventureWorksDW_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf
8 1 ROWS AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf
8 2 LOG AdventureWorks_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf
9 1 ROWS AdventureWorksLT_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf
9 2 LOG AdventureWorksLT_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorksLT_Log.ldf
10 1 ROWS sql911 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
10 2 LOG sql911_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.LDF
(20 row(s) affected)
- The type_desc is 60 characters wide
- The name is 127 characters wide
- The physical_name is 256 characters wide
This makes it difficult for the web, where there is a lot of horizontal scrolling. It can be controlled by using cast and varchar.
cast varchar
SELECT database_id,FILE_ID,
CAST(type_desc AS VARCHAR(6)) AS 'Type',
CAST(name AS VARCHAR(24)) AS 'Name',
CAST(physical_name AS VARCHAR(64)) AS 'Physical name'
FROM sys.master_files;
go
database_id file_id Type Name Physical name
----------- ----------- ------ ------------------------ ----------------------------------------------------------------
1 1 ROWS master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.
1 2 LOG mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog
2 1 ROWS tempdev c:\syv\tempdb.mdf
2 2 LOG templog c:\syv\templog.ldf
3 1 ROWS modeldev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.m
3 2 LOG modellog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modello
4 1 ROWS MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBDat
4 2 LOG MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog
5 1 ROWS ReportServer C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportS
5 2 LOG ReportServer_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportS
6 1 ROWS ReportServerTempDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportS
6 2 LOG ReportServerTempDB_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportS
7 1 ROWS AdventureWorksDW_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventu
7 2 LOG AdventureWorksDW_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventu
8 1 ROWS AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventu
8 2 LOG AdventureWorks_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventu
9 1 ROWS AdventureWorksLT_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventu
9 2 LOG AdventureWorksLT_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Adventu
10 1 ROWS sql911 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.
10 2 LOG sql911_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_
(20 row(s) affected)
- The cast varchar truncates the data.
- The physical name is truncated
SELECT database_id,FILE_ID,
CAST(type_desc AS VARCHAR(6)) AS 'Type',
CAST(name AS VARCHAR(24)) AS 'Name',
CAST(physical_name AS VARCHAR(96)) AS 'Physical name'
FROM sys.master_files;
go
database_id file_id Type Name Physical name
----------- ----------- ------ ------------------------ ------------------------------------------------------------------------------------------------
1 1 ROWS master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
1 2 LOG mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2 1 ROWS tempdev c:\syv\tempdb.mdf
2 2 LOG templog c:\syv\templog.ldf
3 1 ROWS modeldev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf
3 2 LOG modellog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf
4 1 ROWS MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
4 2 LOG MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf
5 1 ROWS ReportServer C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServer.mdf
5 2 LOG ReportServer_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServer_log.LDF
6 1 ROWS ReportServerTempDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf
6 2 LOG ReportServerTempDB_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB_log.LDF
7 1 ROWS AdventureWorksDW_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf
7 2 LOG AdventureWorksDW_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf
8 1 ROWS AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf
8 2 LOG AdventureWorks_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf
9 1 ROWS AdventureWorksLT_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf
9 2 LOG AdventureWorksLT_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorksLT_Log.ldf
10 1 ROWS sql911 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
10 2 LOG sql911_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.LDF
(20 row(s) affected)
