CLEAN
Updated: 30 April 2009
Use CLEAN to remove the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31) from text. Additionally CLEAN removes values 127, 129, 141, 143, 144 and 157.
Syntax
SELECT [wctString].[wct].[CLEAN] (
<@Text, nvarchar(max),>)
Arguments
@Text
is the text value to be cleaned. The @Text argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Types
nvarchar(max)
Remarks
· To remove other characters, consider using the built-in REPLACE function.
Examples
This example will take the string ‘now is the time for all good men to come to the aid of their country’ and insert carriage return (char(31)) and line feed (char(10) character into it.
select 'now is the time ' + Char(13) + char(10) + 'for all good men ' + Char(13) + char(10) + 'to come to the aid '
+ Char(13) + char(10) + 'of their country'
This produces the following result
-----------------------------------------------------------------------
now is the time
for all good men
to come to the aid
of their country
(1 row(s) affected)
If we add the CLEAN function to the SELECT statement
select wct.CLEAN('now is the time ' + Char(13) + char(10) + 'for all good men ' + Char(13) + char(10) + 'to come to the aid '
+ Char(13) + char(10) + 'of their country')
The following result is produced
-------------------------------------------------------------------------------------------------
now is the time for all good men to come to the aid of their country
(1 row(s) affected)