Netezza String Functions are used primarily for string manipulation. An IBM Netezza also supports some of the standard string function along with the PostgreSQL specific functions.
Netezza String Function Usage
Below is the list of Netezza String functions supported:
Function Name |
Description |
| ascii(s) | Returns the numeric ASCII value of the first character in the text string. |
| btrim(s) | Trims spaces from both ends of the string. |
| btrim(s,t) | Trims occurrences of the characters in string t from bothe ends of string s |
| chr(n) | Returns the character with the specified ASCII value. |
| initcap(s) | Capitalizes the first character of each word of the string. |
| instr(s1,s2[,n[,m]]) | Returns the location of substring s2 in string s1. Optional ‘n’ position from which to begin search and ‘m’ a occurrence of the string. |
| length(s) | Returns the length of the string. |
| lower(s) | Converts a string to lowercase. |
| lpad(s,n[,t]) | Pads the left side of string s with characters to create a new string of length n. The optional argument t specifies the padding character; |
| ltrim(s) | Trims spaces from left end of the string. |
| ltrim(s,t) | Trims occurrences of the characters in t string from the left end of string s. |
| repeat(s,n) | Repeats string s n times |
| rpad(s,n) | Spaces pad string s on right to length n. There is an optional third argument (t) that specifies the pad char. |
| rtrim(s) | Trims spaces from right end of string s. |
| rtrim(s,t) | Trims occurrences of the characters in t string from right end of string s. |
| strpos(s,b) | Specifies starting position of substring b in string s. |
| substr(s,p,l) | Returns a substring of string s that begins at position p and is l characters long |
| translate(s,from,to) | Replaces any character in s that matches a character in the from set with the corresponding character in the to set |
| upper(s) | Converts string s to uppercase. |
| strleft(string, n) | Returns left n characters from input string. This is available only in Netezza SQL extension toolkit |
| strright(string, n) | Returns right n characters from input string. This is available only in Netezza SQL extension toolkit |
Netezza String Function Examples
Split string using Netezza String Functions:
TRAINING.ADMIN(ADMIN)=> select * from split_str_test; string ------------- this is in Netezza string test (3 rows) TRAINING.ADMIN(ADMIN)=> select substr(string, 0, instr (string, ' ')) first_part, substr(string, instr (string, ' ') +1) last_part from split_str_test; first_part | last_part ------------+----------- string | test this | is in | Netezza (3 rows) TRAINING.ADMIN(ADMIN)=>
Netezza pad zeros using String functions:
TRAINING.ADMIN(ADMIN)=> select lpad('1234',8,0);
lpad
----------
00001234
(1 row)
TRAINING.ADMIN(ADMIN)=> select rpad('1234.',8,0);
rpad
----------
1234.000
(1 row)
Netezza Sub-string Example:
TRAINING.ADMIN(ADMIN)=> select substr('abcdefg',1,4);
substr
--------
abcd
(1 row)
Netezza Translate Function Example:
Consider string ‘123CDR123’ and replace the CDR with ‘123’
TRAINING.ADMIN(ADMIN)=> select translate('123CDR123','CDR','123');
translate
-----------
123123123
(1 row)
Netezza LEFT and RIGHT Functions Example:
TRAINING.ADMIN(ADMIN)=> select strleft ('1234567891',5);
STRLEFT
---------
12345
(1 row)
TRAINING.ADMIN(ADMIN)=> select strleft ('12',5);
STRLEFT
---------
12
(1 row)
Read:
- Netezza Replace Functions and Examples
- Netezza Date Functions and Examples
- nzsql Command and its Usage
- IBM Netezza Date Format and Conversions
Hello Vithal,
can you recommend a Netezza instruction manual that is user friendly for beginners, please. My job function requires me to extract and manipulated very large datasets. Currently I use base SAS through EG; stand along and pass through to Orable. I would appreciate any suggestions.
Thank you.
Hi Bryan,
You can go through the Netezza database user guide. You can also go through Netezza data loading guide that has lot of examples on data extraction.
Thanks,
Vithal