Apply to:
Microsoft SQL Server 2008 R2
Problem:
Could not Replace Space Character in SQL Server
Note:
Control character Value
* Tab = char(9)
* Line feed = char(10)
* Carriage return = char(13)
1. Create an Example Data
------------------------------------------------------------------------------
------------------------------------------------------------------------------
SET NOCOUNT ON
SELECT '======================'
SELECT 'Test' + CHAR(13) + CHAR(10) + 'Data'
SELECT '======================'
SET NOCOUNT OFF
------------------------------------------------------------------------------
------------------------------------------------------------------------------
2. Results Messages:
----------------------
======================
----------
Test
Data
----------------------
======================
Solution:
* Using syntax Replace.
Check for the space character,
is an Tab - char(9) or Line feed - char(10) or Carriage Return - char(13) ?
If, the space character can be replace with another character,
so we know what the control value.
* The query for check,
A. --> Is an Tab ?
----------------------------------------------------
----------------------------------------------------
select REPLACE
( 'Test
Data',CHAR(9),'_aaa_')
----------------------------------------------------
----------------------------------------------------
Results Messages:
Test
Data
B. ---> Is an Line feed ?
----------------------------------------------------
----------------------------------------------------
select REPLACE
( 'Test
Data',CHAR(10),'_aaa_')
----------------------------------------------------
----------------------------------------------------
Results Messages:
Test
_aaa_Data
C. ---> Is an Carriage return ?
----------------------------------------------------
----------------------------------------------------
select REPLACE
( 'Test
Data',CHAR(13),'_nnn_')
----------------------------------------------------
----------------------------------------------------
Results Messages:
Test_nnn_
Data
D. --> Is an Line Feed and Carriage Return ?
----------------------------------------------------
----------------------------------------------------
select REPLACE
( REPLACE( 'Test
Data',CHAR(13),'_aaa_'
)
,CHAR(10),'_nnn_')
----------------------------------------------------
----------------------------------------------------
Results Messages:
Test_aaa__nnn_Data
In here, the space character in sample data, is using Line Feed and Carriage Return.
Source:
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a87b9fbb-40dc-4a2e-83df-eab150620122