Extract last N digits from a Column

How to extract the last N digits from a column?

　

The substring function is used to extract a part of a string. It has 3 inputs:

Substring(<Column>, <Start>, <End>)

If we don’t specify the <End>, it extracts till the end of column.

In working conditions, generally the columns may contain Blank characters:

Below is an example to deal with such scenarios:

SELECT

TRIM(ARRG.SOURCE_ARRANGEMENT_NUMBER) AS SAN

,

CHAR_LENGTH(TRIM(ARRG.SOURCE_ARRANGEMENT_NUMBER))-6 AS LEN_WITHOUT_BLANK

,

CHAR_LENGTH(ARRG.SOURCE_ARRANGEMENT_NUMBER)-5 AS LEN_BLANK

,

SUBSTRING(TRIM(ARRG.SOURCE_ARRANGEMENT_NUMBER) FROM CHAR_LENGTH(TRIM(ARRG.SOURCE_ARRANGEMENT_NUMBER))-5 FOR CHAR_LENGTH(TRIM(ARRG.SOURCE_ARRANGEMENT_NUMBER))) as Profit_Centre

FROM TA_62917_QRMDB.ARRANGEMENT ARRG;
 

SAN	LEN_WITHOUT_BLANK	LEN_BLANK	Profit_Centre
1088713	2	27	88713
6002804180	5	27	04180
1211176	2	27	11176