Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Question Unanswered: Last occurence of a string

    Hi....

    I have a sql like this..

    select distinct mpur_locdev_fullpath from security.moduleprog_rights

    I returns a result set like this..

    abc/def/ghi/jkl

    So nw i need to get the above value like ..

    jkl

    I need to do it in the sql...

    Can anyone help me....

    Sorry for my bad english....

    thnxxxxxxxx

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    So... This table holds multiple values in a single field?

    Will you always need the last substring value in the field? If yes, use PostgreSQL's substr function, along with the length and strpos functions.

    A simpler (although non-standard) function is PostgreSQL's SPLIT_PART function. You pass it the string, the delimiter, and the field, and it returns the substring.

    Select SPLIT_PART (YourFieldName, '/', 4) from YourTable

    would return the 4th substring that is separated using the forward slash.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2007
    Posts
    11

    Question

    Quote Originally Posted by loquin
    So... This table holds multiple values in a single field?

    Will you always need the last substring value in the field? If yes, use PostgreSQL's substr function, along with the length and strpos functions.

    A simpler (although non-standard) function is PostgreSQL's SPLIT_PART function. You pass it the string, the delimiter, and the field, and it returns the substring.

    Select SPLIT_PART (YourFieldName, '/', 4) from YourTable

    would return the 4th substring that is separated using the forward slash.
    Thnx but the problem is...
    last occurence of the forward slash differs in record by record.
    Itz allwayse not the 4 thats d problem...

    Is there any function goes in reversed order to search a delimiter?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    sure. the substr function.

    ref the PostgreSQL documentation.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jan 2007
    Posts
    11

    Question

    Quote Originally Posted by loquin
    sure. the substr function.

    ref the PostgreSQL documentation.


    I didnt get d answr for my question yet...
    Anyway tel me is there a way to count how many time a special delimiter is occurred in a string.......

    Eg: - ABC/DEF/GHI/JLK

    Here '/' is occurred 3 time.....
    Is there function to count it..?

    Plz help me....
    Thnxxxxxxxxx

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one way to count how many delimiters are in a string is to compare the length of the string to what the length is after you use the REPLACE function to replace each delimiter with an empty string

    length(str) - length(replace(str,'/','') = number of delimiters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Posts
    11

    Smile

    Quote Originally Posted by r937
    one way to count how many delimiters are in a string is to compare the length of the string to what the length is after you use the REPLACE function to replace each delimiter with an empty string

    length(str) - length(replace(str,'/','') = number of delimiters



    Gr8 idea.........
    Thnx my problm solved...............

    Thnk u , thnk u n thnk u..... sooooo much...

    Im new for this forum......
    But i got a gr8 help from ppl here....
    I appreciate that a lot.....

    Thnx for all who tried to help me..........


  8. #8
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    15
    Why not use the substring function, as loquin mentioned, using regexes? It's in the docs linked above. Using the POSIX version, something like:
    Code:
    substring('ABC/DEF/GHI/JLK' from '[A-z0-9]*$')
    or
    substring('ABC/DEF/GHI/JLK' from '[[:alnum:]]*$')
    That should match and return the last alphanumeric characters in the string. It's a lot simpler and cleaner.

    Strange, the \w doesn't seem to be working...
    Last edited by Slush; 01-26-07 at 12:58.

  9. #9
    Join Date
    Jan 2007
    Posts
    11

    Cool

    Quote Originally Posted by Slush
    Why not use the substring function, as loquin mentioned, using regexes? It's in the docs linked above. Using the POSIX version, something like:
    Code:
    substring('ABC/DEF/GHI/JLK' from '[A-z0-9]*$')
    or
    substring('ABC/DEF/GHI/JLK' from '[[:alnum:]]*$')
    That should match and return the last alphanumeric characters in the string. It's a lot simpler and cleaner.

    Strange, the \w doesn't seem to be working...
    Thnx yo.......But i dnt uncerstand..this..
    Code:
      
    [A-z0-9]*$ and [[:alnum:]]*$
    Could u plz explain me in details...

    Thnx... a lot.....

  10. #10
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    15
    There's a LOT to explain about regexes. They're very powerful. Look at the wikipedia entry.

    Those two I used above are nearly equivalent.
    The square brackets '[]' any single character in the range within the brackets. So 'A-z0-9' matches any character from A, B, ... , Y, Z, a, b, ..., y, z, 0, 1, 2, ..., 8, 9. The '*' says to match any number of the preceeding subexpression (this case, being the '[A-z0-9]'), so it will match any of those characters any number of times. The '$' denotes the end of string, so it will match the end of string. Taken together, the regex will match the last string of characters that use letters and numbers (case-insensitive).
    The '[[:alnum:]]' uses the '[]' on the outside. The inside is '[:alnum:]'. The '[:XXX:]' denotes special character classes, this case being the alphanumeric character class. Alphanumeric is the same as the previous one, but it also inclues the underscore '_' character.

    Regexes aren't the easiest thing to learn, but they're definitely powerful and useful. You can do many wonderful things with them.

Posting Permissions

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