trim on column

1. Senior Member
Join Date
Mar 2001
Location
south africa
Posts
401

## trim on column

I have column where the application insert date and productcomments

Date - Purchasereason
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. 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.

3. Senior Member
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. 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?

5. 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;```

6. Senior Member
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 '.'