0

I have done my due diligence and am still stuck. I have a situation where I need to locate where the last existing same character is in a search string, then check to see what follows that character. I think I need to use RLIKE to do this but maybe there is another way. Example table.column content string:

MSH|Stuff|morestuf|||
PID|stuff|morestuff|morestuffofanotherlength||wejustfollowedablank|morestuff2||^1^|||..|||||..|.||.

I what to count 19 pipes | after the keyword PID, then see if the very next character is NOT a | (there may be more than 19 pipes, or there may be less and I dont want to blow up if there are not 19 to look for)

I know I can use the LOCATE function to find my starting position after PID, not sure how to pass that position into an expression to begin counting 19 occurrences of | beyond the start position.

A partial regexp in i.e. pspad would look something like '\(.+\)PID[|]{19}' I cant get this to not err in mysql so either the syntax is different or I am completely on the wrong track.

Any suggestions?

Avinash Raj
  • 172,303
  • 28
  • 230
  • 274
user2433754
  • 9
  • 5
  • change your regex to `\(.+\)PID([^|]*[|]){19}[^|]*$` – Avinash Raj Oct 17 '14 at 16:34
  • I am having difficulty understanding the expression. It is not working, so I am trying to tweak it but nothing I do works. i am guessing I just don't get what each character/command is doing. Can you describe \(.+\.... etc (ie what does each character in combination do). I am completely new to regexp especially in mysql and I have dug into resources but it just isn't making sense. Thanks for your assist! – user2433754 Nov 03 '14 at 13:44
  • I've almost got it. The *$ at the end was throwing me off. If I understand correctly that is string terminator. My string continues, I just want to stop searching and return the result if the next character following the 19th | is a number. So I tried as an example: \(.+\)PID([^|]*[|]){19}[^|[0-9]] but that doesnt work. But if I put the exact character in that I know is present it works: \(.+\)PID([^|]*[|]){19}[^|3] but I cant hardcodeit. The next character can be anything 0-9. – user2433754 Nov 13 '14 at 04:57
  • Ok I dont almost have it. varying the counter {19} doesnt seem to be stopping at a consistent point. i.e. I can hardcode what I think the next character is and it will sometimes return sometimes not. Are the presence of ^ (reserved command) in the search text causing problems? I wish there was a way to select rlike and see what position it is at but I am not finding any posts how to do that, only that it cannot be done. – user2433754 Nov 14 '14 at 16:19

0 Answers0