Trim Trailing Blanks in SQL Queries
If you like to copy-and-paste the text output from SQL SELECT statements, but don't like the wasted space of trailing blanks in wide varchar columns, you might find this script helpful. It will run your SQL SELECT statement and output the results so that the trailing blanks in varchar columns is neatly trimmed off.
Take a statement like this, run in the NorthWind database:
SELECT CustomerId, Address, City, Region, PostalCode FROM Customers WHERE Country = 'USA'
and run it like this:
EXEC spd_Tool_Reformat_Query_Results 'SELECT CustomerId, Address, City, Region, PostalCode FROM Customers WHERE Country = ''USA'''
/***************************************************************************************************
Written by:Jesse McLain
Purpose:Given a SQL SELECT statement, this spd will run it, saving the results in a temp
table, and query the temp table for the max size of the varchar columns. It will
then rerun the query, and limit the size of the output of those varchar columns
to the max size it discovered. Typically this spd will be used to save metadata
in an ASCII text report.
Input Parameters:see below
Output Parameters:none
Called By:user
***************************************************************************************************/CREATE PROCEDURE [dbo].[spd_Tool_Reformat_Query_Results]
(
@sql varchar(8000),-- the SQL SELECT statement to generate the results
@print_sql char(1) = 'N'-- 'Y' if user wants to see SQL used to reformat results
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
IF @sql = 'help' OR @sql = '' OR @sql IS NULL OR LEFT(@sql, 6) <> 'SELECT'
BEGIN
PRINT 'PROCEDURE [dbo].[spd_Tool_Reformat_Query_Results] - parameters:'
PRINT '@sql varchar(8000)-- the SQL SELECT statement to generate the results'
PRINT '@print_sql char(1) = ''N''-- ''Y'' if user wants to see SQL used to reformat results'
RETURN
END
-- normalize incoming SQL statement; make sure that the only white space is space char:
SET @sql = REPLACE(@sql, CHAR(13), ' ')
SET @sql = REPLACE(@sql, CHAR(10), ' ')
SET @sql = REPLACE(@sql, CHAR(9), ' ')
WHILE CHARINDEX(' ', @sql) > 0 SET @sql = LTRIM(RTRIM(REPLACE(@sql, ' ', ' ')))-- replace double space with single
DECLARE @fnd smallint
DECLARE @fnd2 smallint
DECLARE @sql_target varchar(100)
SET @sql_target = ''
DECLARE @tmp varchar(8000)
-- we need to insert an "INTO" clause into the SQL:
SET @fnd = CHARINDEX(' INTO ', @sql)
IF @fnd > 0
BEGIN
-- if there already is one, then we replace it:
SET @tmp = SUBSTRING(@sql, @fnd + 6, DATALENGTH(@sql) - @fnd)
SET @fnd2 = CHARINDEX(' ', @tmp)
IF @fnd2 > 0
BEGIN
SET @sql_target = LEFT(@tmp, @fnd2 - 1)
SET @sql = REPLACE(@sql, ' INTO ' + @sql_target, ' INTO ##Tmp_Query_Results')
END
END
ELSE-- otherwise, the "INTO" clause goes before the "FROM" clause:
BEGIN
-- if there's not already an 'INTO' clause, then we'll find the 'FROM' clause and
-- insert our new 'INTO' just before it:
SET @fnd = CHARINDEX(' FROM ', @sql)
IF @fnd > 0
BEGIN
SET @sql = STUFF(@sql, @fnd, 0, ' INTO ##Tmp_Query_Results')
END
END
IF EXISTS(SELECT 1 FROM TempDb.dbo.Sysobjects WHERE Name = '##Tmp_Query_Results' AND Type = 'U') DROP TABLE ##Tmp_Query_Results
EXEC(@sql)
CREATE TABLE #Tmp_Col_Max_Size (Col_Max_Size int)
DECLARE @ColName varchar(200)
DECLARE @ColType varchar(20)
DECLARE @ColLength smallint
DECLARE @Col_Max_Size int
DECLARE Tmp_Table_Structure_Cursor CURSOR FOR
SELECT
ColName = C.Name,
ColType = T.Name,
ColLength = C.Length
FROM TempDb.dbo.SysColumns C
JOIN TempDb.dbo.SysObjects O ON O.Id = C.Id
JOIN TempDb.dbo.SysTypes T ON T.XType = C.XType
WHERE O.Name = '##Tmp_Query_Results'
AND T.Name <> 'sysname'
ORDER BY C.ColOrder
OPEN Tmp_Table_Structure_Cursor
DECLARE @sql_refmt varchar(8000)
SET @sql_refmt = 'SELECT ' + CHAR(13) + CHAR(10) + CHAR(9)
FETCH NEXT FROM Tmp_Table_Structure_Cursor INTO @ColName , @ColType, @ColLength
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColType IN ('char', 'varchar', 'nchar', 'nvarchar')
BEGIN
TRUNCATE TABLE #Tmp_Col_Max_Size
IF @ColType IN ('nchar', 'nvarchar')
SET @sql = 'SELECT MAX(DATALENGTH(CONVERT(varchar(8000), ' + @ColName + '))) FROM ##Tmp_Query_Results'
ELSE
SET @sql = 'SELECT MAX(DATALENGTH(' + @ColName + ')) FROM ##Tmp_Query_Results'
INSERT INTO #Tmp_Col_Max_Size EXEC(@sql)
SELECT @Col_Max_Size = Col_Max_Size FROM #Tmp_Col_Max_Size
IF @Col_Max_Size IS NULL
SET @sql_refmt = @sql_refmt + @ColName + ' = LEFT(' + @ColName + ', 4), '
ELSE
SET @sql_refmt = @sql_refmt + @ColName + ' = LEFT(' + @ColName + ', ' + LTRIM(STR(@Col_Max_Size)) + '), '
END
ELSE
SET @sql_refmt = @sql_refmt + @ColName + ', '
SET @sql_refmt = @sql_refmt + CHAR(13) + CHAR(10) + CHAR(9)
FETCH NEXT FROM Tmp_Table_Structure_Cursor INTO @ColName , @ColType, @ColLength
END
CLOSE Tmp_Table_Structure_Cursor
DEALLOCATE Tmp_Table_Structure_Cursor
SET @sql_refmt = LEFT(@sql_refmt, LEN(@sql_refmt) - 5) + CHAR(13) + CHAR(10) + 'FROM ##Tmp_Query_Results'
IF @print_sql = 'Y'
PRINT REPLACE(@sql_refmt, '##Tmp_Query_Results', '???')
PRINT ''
EXEC(@sql_refmt)
DROP TABLE ##Tmp_Query_Results
DROP TABLE #Tmp_Col_Max_Size 
