Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: SQL Keyword source - ltrim?

    Is there a keyword reference for Oracle PLSQL out on the net somewhere?


    I have to take the left 20 characters of a string. I'm used to dealing with a Left$(char,int) function. It appears that I have to use an RTRIM() instead, but I don't know what the total length is, so I'm looking for a function to do that . I would typically do something like rtrim(string, 20- len(string)) but apparently I cant do that either.
    Anyone have any ideas?

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    A good place to start is with tahiti.oracle.com, and then choose your db version. All of the documentation is out there.

    Sounds like you can use substr here since you always want to ignore the first 20 characters. Of course if the string is less than 21 characters, this would not work.

    Code:
    select substr(column, 21)
      from your_table
    I think this would be the easiest way personally.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Actually he wants to "take the left 20 characters":

    Code:
    SELECT SUBSTR(col,1,20) ...FROM....


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Perfect - Thanks for all the help.
    Good to know I have a substr() to work with!

    And thanks for the url - I search the forums, but sometimes there just isn't a straight forward dictionary out there.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Link to sql functions:
    PHP Code:
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Quote Originally Posted by LKBrwn_DBA

    Actually he wants to "take the left 20 characters":

    Code:
    SELECT SUBSTR(col,1,20) ...FROM....


    Yea see I read that quickly and saw the word trim, and figured he wanted to take OUT the first 20 characters reading helps!
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Going back to your first post, you can use length functions too. Just change LEN to LENGTH. It's commonly used in the example of finding the last N characters in a string: select substr(mystring, length(mystring)-20, length(mystring) from dual;
    I hope that helps,

    Quote Originally Posted by jer99
    Is there a keyword reference for Oracle PLSQL out on the net somewhere?


    I have to take the left 20 characters of a string. I'm used to dealing with a Left$(char,int) function. It appears that I have to use an RTRIM() instead, but I don't know what the total length is, so I'm looking for a function to do that . I would typically do something like rtrim(string, 20- len(string)) but apparently I cant do that either.
    Anyone have any ideas?
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You don't have to work about short strings with substr. If the column is less then the total length specified in substr, it will return the shorter string.

    SQL> select substr('1234567890123456789012345',1,20) from dual;

    SUBSTR('123456789012
    --------------------
    12345678901234567890

    SQL> select substr('123456789012345',1,20) from dual;

    SUBSTR('1234567
    ---------------
    123456789012345
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by markrem
    Mark
    Author, Oracle Database 10g: From Nuts to Soup
    Why would you go backwards from nuts to soup? I'm confused.

    Or are you making nut soup?

Posting Permissions

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