Results 1 to 10 of 10

Thread: LTRIM function

  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: LTRIM function

    Hi Friends ,

    In Oracle we have LTRIM function that takes two arguments .
    In Oracle the following example trims all of the left-most x's and y's from a string:

    SELECT LTRIM('xyxXxyLAST WORD','xy')
    FROM DUAL;

    Result : XxyLAST WORD
    I tried to reproduce this functionality in MySQL, but LTRIM function in MySQL accepts only one argument . I tried with
    TRIM(LEADING 'xy' from 'xyxXxyLAST WORD')
    But this gives a different result .

    Please suggest me .

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    suggest you what?

    the example you gave doesn't make sense

    and what if there is no immediate equivalent function -- can you explain in words what you want to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It doesn't surprise me that Oracles LTRIM doesn't work it looks a very very odd way of naming/implementing a REPLACE function, every one else Im aware of uses LTRIM/RTRIM./TRIM to remove leading or trailing spaces

    MySQL :: MySQL 5.1 Reference Manual :: 11.4 String Functions
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TRIM can remove other characters besides spaces

    the problem with the given example is that while the oracle function appears like it might be removing sets of pairs of characters, the given example doesn't look right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agreed oracle LTRIM offers that, but MySQL LTRIM doesn't. Like you Im not convinced that there is a direct MySQL repalcement for what is claimed for the Oracle LTRIM fucntion... the closest I coudl see was REPLACE. but y'never know
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by r937
    the problem with the given example is that while the oracle function appears like it might be removing sets of pairs of characters, the given example doesn't look right
    With the LTRIM function, Oracle treats the characters in the second argument as a list. It will remove any of the characters, in any combination, on the left side of the first argument until it reaches a character that is not in the list.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think MySQL is working on using regular expressions with the replace command - this would then handle this sort of thing easily. Sadly it hasn't been implemented yet.

    Of course you could also avoid hiring data entry people with Tourette's and then you won't get the XXXX at the start of each name.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by buckeye234
    ... until it reaches a character that is not in the list.
    aha, the penny drops, thanks very much

    TRIM(LEADING 'y' FROM TRIM(LEADING 'x' FROM daString))

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

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Won't that just leave you with "xXxyLAST WORD"?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you're right, mike, good catch

    sushma, perhaps you should be trimming in the front end app?
    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
  •