query to get partial data in a column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: query to get partial data in a column

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    query to get partial data in a column

    hello

    need help to get part of description column

    tablerders
    orderid and description

    data is
    (1,hdmi cable 1cd)
    (2,hdmi cable 1dc)
    (3,logitech mouse 1ab)

    i need to query the description but it should show only from 1

    1,1cd
    2,1dc
    3,1ab

    how do i get that

    thanks for your help

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Is it always a 1 ? If so, the following should work:

    Code:
    SQL> select * from orders;
    
       ORDERID DESCRIPTION
    ---------- -------------------------
             1 hdmi cable 1cd
             2 hdmi cable 1dc
             3 logitech mouse 1ab
    
    SQL> select orderid,
      2         regexp_substr(description, '1[^ ]*$') description
      3  from orders;
    
       ORDERID DESCRIPTION
    ---------- -------------------------
             1 1cd
             2 1dc
             3 1ab

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you please explain the regular expresssion

    '1[^ ]*$'


    I understand
    * -------------> match any number of characters
    $-------------->End of line
    1-------------->Number 1

    what is [^ ] ?

    regards
    Hrishy

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    *  --> will match ZERO or more characters of the preceding character/expression
    [ ] --> indicates a character list and the ^ is used to negate the character list
              (ie. match ALL characters NOT IN this list).

    therefore, [^ ]* will match ZERO or more characters EXCEPT spaces

  5. #5
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    THANK YOU for your your help and time ..

    not sure what i was thinking ,its supposed to be T instead of 1 and yes it always starts with T and we have lot of "," or "-" in between description but not after T.Also we have spaces like T cd or T DC

    do we need to use different expression if its charachter instead of a number..

    SQL> select * from orders;

    ORDERID DESCRIPTION
    ---------- -------------------------
    1 hdmi cable Tcd
    2 hdmi cable Tdc
    3 logitech mouse Tab
    Last edited by prodadmin; 01-06-2010 at 09:36 AM. Reason: explian

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    The following should work based on that assumption:

    Code:
    ltrim(regexp_substr(description, ' T.*$'))

  7. #7
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    Yes that worked,THANK YOU again for your quick help

    i see where i went wrong i kept modifying you orginal advise without the dot (t*$) and never got result.you last advise worked great

Posting Permissions

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



Click Here to Expand Forum to Full Width