• Adam Thurgar

Removing control characters from output


Whilst reviewing some queries recently I was using the text column from sys.dm_exec_sql_text using the plan_handle from sys.dm_exec_query_stats. When cutting and pasting the results from SSMS into Excel I had an issue as the text column results had control characters. To get around this problem I changed the query to put the results into a temporary table and then used the following to replace the control characters when selecting from the temporary table.

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(columnname, CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ')))

Where:

char(9) tab

char(10) linefeed char(13) carriage return

This ending up saving time in not having to format the results.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t