Archive

Posts Tagged ‘Char(13)’

SQL Server PDW Replace Special Characters

September 3, 2013 Leave a comment

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