MySQL Finding the End Position of String within String

Question

I'm looking for a way to identify the end position of a matched string within a string. If I have the following text in a field:

The book is on the shelf

And I want to find the ending position of the text "on", I can find the start using locate(), which returns the starting position, but I'm trying to get the ending position as well.

My expected result then would be something like this:

Text                     | End Position
----------------------------------------------
The book is on the shelf | 14

I have a query that uses a LIKE to find matching text with wildcards. I'm searching across a field that can contain 5000 characters of text, and of course trying to find the matching text in this field is time consuming. Therefore, I was hoping there is a way to basically show where the LIKE has matched the text. Maybe both the start and ending positions.

medina · Accepted Answer · 2013-04-23 09:03:56Z

3

SELECT @a := 'The book is on the shelf', 
    LOCATE('on', @a) AS pos_initial, 
    (LOCATE('on', @a) + LENGTH('on')) as pos_final;

answered Apr 23, 2013 at 9:03

medina

8,2294 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Finding the End Position of String within String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related