trim on column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: trim on column

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

    trim on column

    I have column where the application insert date and productcomments

    Date - Purchasereason
    01/19/2005 - product purchased:100000. forupgrade
    02/30/2005 - Product Purchased:134783. for newins office213

    Now i need to display the number part only(100000 and 134783)
    if i use ltrim i am able to remove intial part

    How do i get only like 134783 or 100000

    any little help is much appreciated

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If it's always EXACTLY in the same place:
    SUBSTR(my_string, 30, 6) will find it.
    (the 30 is approximate, I'm incapable of counting correctly - for me it goes: 1,2,3,more,lots,too-many.)

    Otherwise you'll have to use INSTR to find the : and . and build a substr with the result.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    Yes , it starts every time at the same place but the number varies , some times it will be 5 and some times it could be 7

    Thanks for the help



    Originally posted by DaPi
    If it's always EXACTLY in the same place:
    SUBSTR(my_string, 30, 6) will find it.
    (the 30 is approximate, I'm incapable of counting correctly - for me it goes: 1,2,3,more,lots,too-many.)

    Otherwise you'll have to use INSTR to find the : and . and build a substr with the result.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Then it will be roughly:

    SUBSTR(my_string,
    INSTR(my_string, ':') + 1,
    INSTR(my_string, '.') - INSTR(my_string, ':') - 1)

    Now the question is, can you rely on the : and . always being there?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Originally posted by DaPi
    Then it will be roughly:

    SUBSTR(my_string,
    INSTR(my_string, ':') + 1,
    INSTR(my_string, '.') - INSTR(my_string, ':') - 1)

    Now the question is, can you rely on the : and . always being there?
    You beat me to it!

    Code:
    SELECT SUBSTR('01/19/2005 - product purchased:100000. forupgrade',
            INSTR('01/19/2005 - product purchased:100000. forupgrade', ':', 20) + 1,
            INSTR('01/19/2005 - product purchased:100000. forupgrade', '.') - 
            INSTR('01/19/2005 - product purchased:100000. forupgrade', ':', 20) - 1 )
      FROM dual;
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thank you guys ! you are awesome!!

    i was trying and hung something like SUBSTR(Purchase,30,INSTR(purchase, ':')

    You guys made it easy ..thanks it works , yes the application inserts ':' and '.'

    Thanks for your time.

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