The issue is I have a table with an identifier column that is - seperated (example of a column value could be: TOM-XYZ12356-DIM34345-TE123456 or TOM-XYZR3455667AB-RXB75855-RYB434553)
My main focus is to try and extract that 2nd part of the identifier. In the above examples it would be (XYZ12356 and XYZR3455667AB). I tried something simple below but keep getting the error "Attribute '-' not found"
SELECT substr(identifier,1,Charindex("-",identifier) -1)
FROM TableA
Perhaps i am not specifying the start point correctly or maybe I am not using the right function. Can i use some ther function other than Charindex?
You can use SUBSTR and INSTR to get the results you want.
select * from tableA;
COL1
--------------------------------
TOM-XYZ12356-DIM34345-TE123456
(1 row)
SUBSTR works as you would expect (although it seems your parameters are out of order) and you can 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;
SUBSTR
----------
XYZ12356
(1 row)
Check the syntax for SUBSTR and INSTR here.