I am facing a challenge with PQL PATINDEX and any help is highly appreciated.
My requirement is to get the number from the given string pattern like ‘MXMXG5 - 5 - Mexico Domestic Road’ (In some cases it is double digit like 15).
I’m using SUBSTRING function and PATINDEX together. When i’m using PATINDEX(‘MXMXG5 - 5 - Mexico Domestic Road’, ‘% %’,1) Here it is returning correctly the 1st occurrence of space which is 7.
when i am searching for the Second Occurrence position, PATINDEX(‘MXMXG5 - 5 - Mexico Domestic Road’, ‘% %’,2) it is giving me the occurrence position 13 where as i am expecting 9.
Thanks for the assistance.