If you are migrating to Netezza from other relational databases such as Oracle, SQL Server etc, then you might have noticed Netezza left and right functions are not available.
There are other alternatives string functions to Netezza left and right functions, such as you can use the built in SUBSTR function or STRLEFT and STRRIGHT functions that are provided in Netezza SQL Extension tool kit. You have to download and install this SQL extension toolkit explicitly on required database.
Netezza strleft Function
The strleft() function returns the leftmost n characters from an input string.
Syntax:
The strleft() function has the following syntax:
strleft(varchar input, int n);
The input value specifies the varchar or nvarchar value from which the characters are returned. The n value specifies the number of characters to return.
Netezza SQL Extension Toolkit Function reference
You should refer the strleft and strright functions from the database where Netezza SQL extension toolkit is installed, otherwise you will get error like below:
TRAINING.ADMIN(ADMIN)=> select strright ('012345678910',5); ERROR: Function 'STRRIGHT(UNKNOWN, INT4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts
Netezza strleft Function Examples
TRAINING.ADMIN(ADMIN)=> select SYSTEM..strleft ('012345678910',5); STRLEFT --------- 01234 (1 row) TRAINING.ADMIN(ADMIN)=> select SYSTEM..strleft ('Netezza strleft Function is very useful',24); STRLEFT -------------------------- Netezza strleft Function (1 row)
Netezza strright Function
The strright() function returns the rightmost n characters from an input string.
Syntax:
The strright() function has the following syntax:
strright(varchar input, int n);
The input value specifies the varchar or nvarchar value from which the characters are returned. The n value specifies the number of characters to return.
Netezza strright Function Examples
TRAINING.ADMIN(ADMIN)=> select SYSTEM..strright ('012345678910',5); STRRIGHT ---------- 78910 (1 row) TRAINING.ADMIN(ADMIN)=> select SYSTEM..strright ('Netezza strleft Function is very useful',12); STRRIGHT -------------- very useful (1 row)
SUBSTR as a Netezza LEFT and RIGHT Functions Alternative
If you do not have Netezza SQL Extension toolkit installed, you can use the Netezza substr function as an alternative.
TRAINING.ADMIN(ADMIN)=> select SUBSTR('ABCDED',1,2); SUBSTR -------- AB (1 row) TRAINING.ADMIN(ADMIN)=> select SUBSTR('ABCDED',5,2); SUBSTR -------- ED (1 row)
Read:
- Download and Install Netezza SQL Extension tool kit
- Different types of Netezza Trim Functions and Examples
- Netezza Array Functions and Examples
- Netezza String Functions and Examples
- IBM Netezza Extract Function and Examples
- Netezza group_concat alternative Working Example
- Replace Function and Examples