On Thu, Mar 15, 2001 at 01:18:57PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> >> Is there a way to just remove the "_opto" from the end of the string?
>
> > If you have exactly one known string to (optionally) remove, this works
> > (and even works if the string is missing. Watch out for the early
> > occurance of substring problem, though!):
>
> > test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1));
>
> My first thought for any moderately complicated string-bashing problem
> is to write a function in pltcl or plperl ... they are much stronger in
> string manipulation than SQL itself is.
Agreed, hence the caveats about 'exactly one string, that you know ahead of
time, and never appears as a substring ...'
But it _can_ be done, it's just not pretty. And it _is_ standard SQL:
here's the SQL92 spelling of the above:
SELECT SUBSTRING ('center_out_opto' FROM 1 FOR (POSITION ('_opto' IN 'center_out_opto') - 1));
Ross