CLEAN

Updated 2023-11-10 14:29:46.500000

Syntax

SELECT [westclintech].[wct].[CLEAN] (
   <@Text, nvarchar(max),>)

Description

Use the scalar function 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.

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 Type

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(13)) 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.

{"columns":[{"field":"column 1"}],"rows":[{"column 1":"now is the time"},{"column 1":"for all good men"},{"column 1":"to come to the aid"},{"column 1":"of their country"}]}

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.

{"columns":[{"field":"column 1"}],"rows":[{"column 1":"now is the time for all good men to come to the aid of their country"}]}