Netezza LEFT and RIGHT Functions

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

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.

netezza left and right functions

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:

Tags: , , ,