Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    212

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hint: use INSTR inside REVERSE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    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;

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by ozzii View Post
    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]

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by mike_bike_kite View Post
    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`

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    Actually what I wanted was to count the occurrances of a given character. I should have explained it better.
    you can say that again!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •