How to insert a non-breaking space in PL/SQL

I've found various threads about trimming non-breaking spaces in Oracle PL/SQL but I haven't found a single one telling me how to add one at the beginning of a string. I've tried &nbsp and chr(160) but none of them work. I need a space at the beginning of a line that will not be trimmed by standard tools I cannot influence/configure differently.

Thanks in advance infy

Solution

Use CHR(160).

SELECT DUMP(CHR(160)),
       DUMP(TRIM(CHR(160)))
FROM   DUAL;

Outputs:

DUMP(CHR(160))	DUMP(TRIM(CHR(160)))
Typ=1 Len=1: 160	Typ=1 Len=1: 160

In the left column, CHR(160) gives a string with length 1 containing a single character with the ASCII code 160 (a non-breaking space) and applying TRIM to it, in the right column, you get the same output.

Alternatively, use ' ' (a string containing the Non-Breaking space character):

SELECT DUMP(' '),
       DUMP(TRIM(' ')),
       LENGTH(' ')
FROM   DUAL;

Which outputs:

DUMP(' ')	DUMP(TRIM(' '))	LENGTH(' ')
Typ=96 Len=2: 194,160	Typ=1 Len=2: 194,160	1

or UNISTR('\00A0') (160 decimal = A0 hexadecimal):

SELECT DUMP(UNISTR('\00A0')),
       DUMP(TRIM(UNISTR('\00A0'))),
       LENGTH(UNISTR('\00A0'))
FROM   DUAL;

DUMP(UNISTR('\00A0'))	DUMP(TRIM(UNISTR('\00A0')))	LENGTH(UNISTR('\00A0'))
Typ=1 Len=2: 0,160	Typ=1 Len=2: 0,160	1

In both those cases, a multi-byte character is generated. For the literal it is a VARCHAR2 and for UNISTR it is a NVARCHAR2 (which is why the first byte of the multi-byte character differs, as they are using different encodings).

fiddle