I believe I have come across a bug in the substring(text from pattern for escape) function.
What I am attempting to do is take a string like 'QMy NameQ' , strip off leading/and/or/trailing Qs and return 'My Name'.
The substring() call that I have coded is substring(xxx from 'Q?#"%#"Q?' FOR '#')
My understand of this is:
Q? -- matches 0 or 1 occurrences of Q (the leading one, if present)
#" -- starts data collection
% -- matches any number of characters
#" -- ends data collection
Q? -- matches 0 or 1 occurrences of Q (the trialing one, if present)
What appears to be happening is that the Q? on the tail is being matched, but the Q is also being included in the collected data.
The attached PSQL test.sql file creates a table, populates it with 7 test cases with my expected results, and then executes substring() against it.
There is an attached test_output.txt file is the PSQL output that illustrates the problem. The column "Error" is true when the actual value disagrees with what I expected.
Robert Schreiber
410-392-9553
PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
Running under Windows 10 64-bit.