I had a reason this week to perform a substring on a character in Netezza this week, something I have not had a need to do before. The process was not as straightforward as I would have thought, since the command is explained as a static position command, and the IBM documentation, honestly, wasn’t much help. Knowing full well, that text strings are variable having to provide a static position is not terribly useful in and of itself. So, we need to use an expression to make the substring command flexible and dynamic.
I did get it work the way I needed, but it took two commands to make it happen:
- The First was the ’instr’ command to identify the field and character I wanted to substring on: instr(<<FIELD_NAME>>,’~’) as This provides the position number of the tilde (~).
- The second was the ‘substr’ command in which I embedded the ‘instr’ command: substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) )
This worked nicely for what I needed, which was to pick out a file name from the beginning of a string, which was delimited with a tilde (~)
Substring on a Character Command Format
- This format example starts with position zero (0) as position 1 of substring command and goes to the first tilde (~) as position 2 of the substring command.
| Select <<FIELD_NAME>>
, instr(<>,’~’) as pos2 , substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) ) as Results From <<Table_Name>> where <<Where_Clause>>;
|
