How can i find last occurence of a substring in string?

ozzii · #1 (**permalink**) 01-03-10, 14:10

Hi

I can use MySQL INSTR(str,substr) function to return the position of the first occurrence of substring substr in string str. Does any one know if there is a function or a way to return the the position of the last occurrence of substring substr in string str?

Regards

r937 · #2 (**permalink**) 01-03-10, 14:14

hint: use INSTR inside REVERSE

ozzii · #3 (**permalink**) 01-03-10, 14:53

Quote:

Originally Posted by r937

hint: use INSTR inside REVERSE

not sure how you can do a work around using REVERSE but i've managed the follwoing workaround.

Code:

select instr('1,2,3,4,5', substring_index('1,2,3,4,5', ',', -1)) - 1 col;

ozzii · #4 (**permalink**) 01-03-10, 14:58

Quote:

Originally Posted by ozzii

not sure how you can do a work around using REVERSE but i've managed the follwoing workaround.

Code:

select instr('1,2,3,4,5', substring_index('1,2,3,4,5', ',', -1)) - 1 col;

Actually the above wont work if I have the following - its counting each character:

Code:

select instr('10,2,3,4,5', substring_index('1,2,3,4,5', ',', -1)) - 1 col;

[/QUOTE]

mike_bike_kite · #5 (**permalink**) 01-03-10, 17:18

Can you explain why you're trying to do this and provide a little example data. I'm guessing that the db design is a bit suspect.

ozzii · #6 (**permalink**) 01-03-10, 17:40

Quote:

Originally Posted by mike_bike_kite

Can you explain why you're trying to do this and provide a little example data. I'm guessing that the db design is a bit suspect.

Actually what I wanted was to count the occurrances of a given character. I should have explained it better. Any way I've found the following which works a treat:

Code:

SELECT LENGTH('10,2,3,4,5') - LENGTH(REPLACE('10,2,3,4,5', ',', '')) AS `occurrences`

r937 · #7 (**permalink**) 01-03-10, 19:42

Quote:

Originally Posted by ozzii

Actually what I wanted was to count the occurrances of a given character. I should have explained it better.

you can say that again!!

Internet Services

Web Development

Digital Marketing

Consumer Tech