trim on column

[prodadmin]

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

[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.

[prodadmin]

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.

[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?

[gandolf989]

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!

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;

[prodadmin]

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.