G'Day!
I'm getting strange results when using a combination of SUBSTR and INSTR to extract a string form a longer one.
It works on some values and not others.
This is the code I'm using, and further below examples of the long string.
SUBSTR(INDEX, -(INSTR(INDEX,','))+1 AS SUB_INDEX
I basically want to extract part of the string from the last comma to the end.
That's why I have a '-' in front of the INSTR.
This works fine. It's given me everything from the last comma to the end:
INDEX = MscFunction=1,MscLocs=1,MscSubLocs=1,MscValue=ML-3
SUB_INDEX = 'MscValue=ML-3'
This didn't work. It has returned a few characters before the last comma.
INDEX = MscFunction=1,MscLocs=1,MscLoc=1
SUB_INDEX = 's=1,MscLoc=1'
This one's even stranger!
INDEX = MscFunction=1,MscLocs=1,MscLoc=1,MscAdjacents=1,MscAdjacent=ABC,MscPeers=1,MscPeer=MBA8-MBC9-D3d-HMS
SUB_INDEX = '=MBA8-MBC9-D3d-HMS'
or
SUB_INDEX = '-MBA8-MBC9-D3d-HMS' !!?!?
Why?
Why are they all returning differing results?
The last example seems to be substituting the '=' for a '-', and not including some of the characters after the comma!
(I'm using SQL Developer Version 3.1.07)
Any help would be appreciated.
Thanks,