Getting the last character in an Oracle CLOB column-CodePudding

I am having an issue with getting the last character from an Oracle CLOB column. The same SQL that works on an VARCHAR column doesn't work on a CLOB column and can't figure out why or how to make it work.

(this works in getting the last character from a VARCHAR)

select substr(COLUMN_NAME, length(COLUMN_NAME), 1) from TABLE_NAME

(when applied to a CLOB column, it doesn't return the last character.

select substr(CLOB_COLUMN, length(CLOB_COLUMN), 1) from TABLE_NAME

(Updates below) Per Alex suggestion, I added the dump/cast and get "Typ=1 Len=1: 10" for that column. (I have no idea what that is)

Here is the SQL I was running:

set linesize 32000

select 
length(COLUMN_NAME) as clob_length, 
substr(trim(COLUMN_NAME), length(trim(COLUMN_NAME)) -50 ) as last_50,
SUBSTR(COLUMN_NAME, -1) as last_char,
dump(cast(substr(COLUMN_NAME, -1) as varchar2(1)))
from TABLENAME

I added the SQL I used and the results for one of my rows are:

CLOB_LENGTH: 1227 
LAST_50: shall be permitted to be continued in service 
LAST_CHAR: 
VARCHAR2(1): Typ=1 Len=1: 10

(I am not clear as to what the last value means)

CodePudding user response：

Works for my simple test case:

SQL> create table test (col clob);

Table created.

SQL> insert into test (col) values ('This is a cloB');

1 row created.

SQL> select substr(col, length(col), 1) from test;

SUBSTR(COL,LENGTH(COL),1)
--------------------------------------------------------------------------------
B

Though, it is simpler to use substr(col, -1) (take the last character):

SQL> select substr(col, -1) from test;

SUBSTR(COL,-1)
--------------------------------------------------------------------------------
B
   
SQL>

CodePudding user response：

I added the dump/cast and get "Typ=1 Len=1: 10" for that column

As suspected from your image, your CLOB values end with new lines (character 10, which is what the dump output shows), and probably other whitespace, or perhaps line feed (character 13) if the original data came from, say, Word. That's why the 'last 50' isn't showing 50 'normal' characters - there are multiple blank lines taking up some of those positions.

If you want to ignore all of those trailing characters you can change your trim to include more characters, and apply that to both substr calls; something like:

substr(rtrim(clob_column, chr(32)||chr(9)||chr(10)||chr(13)), -1)

which will remove any combination of spaces, tabs, new lines and line feeds.

db<>fiddle showing the 50 character version now appears as a single line, and the last character is null, '.', 'n', 'e' etc.