Putting together Adrian Klaver's, and David Johnson's suggestions I
think gets to what he was asking for:
# select length('Tomomasaaaaomaaaaaa') - position(reverse('om') in
reverse('Tomomasaaaaomaaaaaa'));
?column?
----------
12
On Mon, Mar 27, 2017 at 12:16 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 03/27/2017 09:03 AM, Brian Dunavant wrote:
>>
>> That does not return the correct answer for the original poster's request.
>>
>> flpg=# select position('om' in reverse('Tomomasaaaaaaaaaaa'));
>> position
>> ----------
>> 15
>> (1 row)
>
>
> It shows the position counting back from the end. If you want counting from
> the front:
>
> aklaver@test=> select (length('Tomomasaaaaaaaaaaa')+ 1) - position('om' in
> reverse('Tomomasaaaaaaaaaaa'));
> ?column?
> ----------
> 4
>
>
>>
>>
>>
>>
>> On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver
>> <adrian.klaver@aklaver.com> wrote:
>>>
>>> On 03/27/2017 08:05 AM, Ron Ben wrote:
>>>>
>>>>
>>>> Hi,
>>>> position(substring in string)
>>>> as listed here:
>>>> https://www.postgresql.org/docs/9.1/static/functions-string.html
>>>> locates sub string in a string.
>>>>
>>>> It doesn't support locateing the substring from the back.
>>>>
>>>> For example:
>>>>
>>>> position('om' in 'Tomomas')
>>>> gives 2
>>>>
>>>> But if I want to locate the first occurance from the back of the string
>>>> it's impossible/
>>>
>>>
>>>
>>> aklaver@test=> select position('om' in reverse('Tomomas'));
>>> position
>>> ----------
>>> 4
>>>
>>>
>>>>
>>>> My suggestion is to create a function
>>>> position(substring in string,order)
>>>> where order can be: begin, end
>>>>
>>>> and it will find the string according to this parameter.
>>>> This is pretty easy to implement and should be a part of the PostgreSQL
>>>> tools.
>>>>
>>>> similar fuctionality exists in trim function where user can specify
>>>> leading or taling parameter
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com