Home > PDW > SQL Server PDW Replace Special Characters

SQL Server PDW Replace Special Characters

If you run across special characters (such as tabs, carriage returns and line feeds) and need to manipulate them in some manner in PDW, you need to use Hex representation since Decimal representation is not available in version (Microsoft SQL Server 2012 – 10.0.4176.0 (X64) Apr 12 2013 19:03:46 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )). See the sql below to better understand:

/*
CREATE TABLE [dbo].[PDWTest] WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = REPLICATE) AS
SELECT CONVERT(varchar(255), 0x746573740A) [TestColumn] UNION ALL
SELECT CONVERT(varchar(255), 0x74657374) [TestColumn] UNION ALL
SELECT CONVERT(varchar(255), 0x7465737409) [TestColumn] UNION ALL
SELECT CONVERT(varchar(255), 0x746573740D) [TestColumn] UNION ALL
SELECT CONVERT(varchar(255), 0x746573740D0A) [TestColumn]
*/

/*
How to translate varbinary to sql representation (Hex = Ascii Character = Decimal):
0x74 = 't' = CHAR(116)
0x65 = 'e' = CHAR(101)
0x73 = 's' = CHAR(115)
0x74 = 't' = CHAR(116)
0x0A = [tab] = CHAR(9)
0x0D = [line feed] = CHAR(10)
0x09 = [carriage return] = CHAR(13)
*/
SELECT TestColumn AS [Standard View]
	,'*' + TestColumn + '*' AS [Standard View With Asterisks]
	,CONVERT(varbinary(8000), TestColumn) AS [Binary View]
  --,REPLACE(TestColumn, CHAR(10), '') AS [This Fails in PDW] --'CHAR' is not a recognized built-in function name.
    ,REPLACE(TestColumn, 0x0A, '') AS [Use Hex Code in PDW]
	,REPLACE(TestColumn, 0x0D0A, '') AS [Multiple Hex codes] --Think of (0x) as a single quote (') used to specify the beginning of a hex code, there is just no closing equivalent in hex
	,REPLACE(REPLACE(REPLACE(TestColumn, 0x0D, ''), 0x0A, ''), 0x09, '') AS [Nested Example]
	,'*' + REPLACE(REPLACE(REPLACE(TestColumn, 0x0D, ''), 0x0A, ''), 0x09, '') + '*' AS [Standard View of fixes]
	,CONVERT(varbinary(8000), REPLACE(REPLACE(REPLACE(TestColumn, 0x0D, ''), 0x0A, ''), 0x09, '')) AS [Hex View of fixes]
FROM PDWTest

The sql statement above produces these results:
Capture

Download SQL

ASCII Table Reference

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: