Netezza SQL, substr, instr usage.

In SQl, one way to manipulate the string is to extract some data from the strings based on some patterns. 

An example, we have strings, TOM-XYZ12356-DIM34345-TE123456 or TOM-XYZR3455667AB-RXB75855-RYB434553.  main focus is to  extract  the string between the 1st and the 2nd '-'.

use INSTR to provide the start character after the first '-', and then calculate the SUBSTR length by substracting the position of the first '-' from the position of the second '-'.

SELECT SUBSTR(col1, instr(col1,'-',1,1)+1 , 

instr(col1,'-',1,2) - instr(col1,'-',1,1) -1 )
FROM tableA; 

If we want to get the string before the first '-', the query will be:

SELECT SUBSTR(col1,1,  instr(col1,'-',1,1)-1 ) FROM tableA;